Need educating on log files

briangriffin · Oct 15, 2014

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 · Oct 15, 2014

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need educating on log files

briangriffin

Programmer

SQLBill

MIS

Similar threads

Part and Inventory Search

Sponsor