×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need educating on log files

thread962-1739151

Need educating on log files

Need educating on log files

(OP)
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.

RE: Need educating on log files

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: What should I know before I post?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close