Is There anyway to disable writing to Transaction

Question

Is There anyway to disable writing to Transaction

ngkh

Mr or Mrs. 500

Points: 531
More actions
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?

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

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

Karen Jackson Say Hey Kid Points: 686 More actions · Answer 1

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

Antares686 SSC Guru Points: 125444 More actions · Answer 2

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.

ngkh Mr or Mrs. 500 Points: 531 More actions · Answer 3

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?

ngkh Mr or Mrs. 500 Points: 531 More actions · Answer 4

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?

philcart SSC-Forever Points: 47796 More actions · Answer 5

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

Green Armchair Hall of Fame Points: 3561 More actions · Answer 6

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.

Jeff W SSCertifiable Points: 5207 More actions · Answer 7

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

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 8

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