Need educating on log files

[briangriffin]

Using SQL Server 2008 R2.

I have a database that is used to store audit information. Recovery mode is simple, and if the entire db got wiped out we could recreate it easily.

One of the tables grew very large - about 230 million records - and the db size is 159 gigs. The log file grew to over 100 gigs and filled up the drive, so I shrank it and attempted to delete most of the records from the offending table.

So far, so good, but when I do a delete - even with a small subset of the records - the log grows incredibly fast. For instance, I just ran a delete of 3.6 million records, and the log grew almost instantly to 50 gigs. Why is this? For this one I don't really need a log at all, but I definitely don't need it to fill up and crash the drive at the least opportune time.

What are some recommendations to get this to behave in a manageable way?

TIA.

 

[SQLBill]

The log file has to maintain all records until the modifications are committed. That is the only way a rollback can happen. One solution is to do the deletes in batches of smaller amounts. You might delete 10,000 rows at a time. Loop through the table and use a BEGIN TRAN <delete> COMMIT TRAN statement. That will commit the transaction after every batch of deletes and should keep the log from growing so much.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875