Stop logging to the transaction log?

[LCannon]

Does anyone know of any SQL code that will tell MS-SQL to not to log to the transaction log?

I'm trying to delete a large number of records from a table but to do it all at once causes the transaction log to fill up. So, I'm thinking that if it was possible to tell SQL not to log the when I'm deleting those records, and then to turn logging back on when all those gone would prevent my transaction log from filling.

 

[nealvenables]

If you want to delete ALL of the records from a table without logging, use Truncate Table.

 

[LCannon]

I don't want to delete all the records in a table nor get rid of the table itself. I'm cleaning out old records from the table which turns out to be a lot of records.

 

[SQLSister]

Off hand I can't think of how I would turn off transaction logging in a delete except through truncate table. I'm fairly sure it is not allowed.

But I would not want to turn off transaction logging on a delete even if I could. Why? What if you needed to restore your database? Those deletes would not be there and so your restore would be innaccurate until the next differntial or full backup. Also, if you are not deleting the whole table, there is a good chance you might accidentally delete the wrong records. Might be nice to be able to restore them. Murphy's law being what it is, you probably wouldn't realize right away you deleted the wrong records and would overwrite your backup with a new one before anyone brought it to your attention.

So what to do? Well first I would backup the transaction log before doing the delete. Then do the delete and backup the transaction log again. At this point, you might want to shrink it. Or you could do the delete in batches and back up the transaction log immediately after each batch to prevent too much growth. See thread183-425873 for an exmaple of how to do things in batches.

 

[tlbroadbent]

Even if you use Simple Recovery mode, SQL must record the deletions in the transaction log in order to be able to rollback uncommitted transaction should an error occur. As SQLSister suggested, deleting in smaller batches is always a good option because it prevents the log from growing very large. It will make to process considerably faster, also. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.