Post reply

Is There anyway to disable writing to Transaction

  • March 5, 2003 at 2:52 pm

    #58995

    Is there anyway for SQL Server to stop writing transaction logs during an insert statment to a particular database? My DB is use for mainly for transformation purposes, doesn't really needs a transaction log, is there anyway to disable writing to transaction log for performance increase?

  • March 5, 2003 at 2:58 pm

    #450351

    Turning on the truncate log at check point in the properties of the database on the options tab will stop logging to the trans log. Also, which I think may be better for your purpose turning on Select Into\Bulk copy will do the same. This is in SQL 2K

  • March 5, 2003 at 3:02 pm

    #450352

    Actually "truncate log at check point " doesn't stop logging it just clears the transactions after a transaction completes. The transaction log is how DBs ensure that if an error in writting occurrs it can undo all changes in a transaction.

  • March 5, 2003 at 3:22 pm

    #450354

    Would the select into/bulk copy command only works when you do select into command? or the same performance can be gained when using insert follow by a select statement?

  • March 5, 2003 at 3:22 pm

    #450353

    Would the select into/bulk copy command only works when you do select into command? or the same performance can be gained when using insert follow by a select statement?

  • March 5, 2003 at 3:58 pm

    #450357

    Check out "Logged and Minimally Logged Bulk Copy Operations" in BOL. It'll give you some tips on how to setup your server/database/procedure to have minimal logging.

    Thanks

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • March 6, 2003 at 2:45 am

    #450375

    I don't believe you can ever prevent sql writing to the log file completely. Even if you are in simple or bulk-logged mode it still writes minimal info to the log.

  • March 6, 2003 at 5:37 am

    #450387

    Select into/bulk copy is also very useful when executing the DBCC DBREINDEX command.

    I do not believe there is a way to stop all entries in the transaction log. You can keep them down in size if needed. I have a database that has a process which runs every 5 minutes..if the log is over 50% then I truncate it with an BACKUP LOG <dbname> WITH TRUNCATE_ONLY. It is an odd situation that I don't have time to go into detail about, but it works well.


    "Keep Your Stick On the Ice" ..Red Green

  • March 10, 2003 at 1:08 am

    #450639

    If you don't like bulk-insert, you can minimize logging with simple-recovery-setting (sql2k) or truncate-log-on-checkpoint (sql7) when using small transactions. This means you have to chop up your input in chuncks you controle. Every rdbms has to have logging to guaranty integrity of data.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply