SQL Server with Mr. Denny

Jun 9 2010   3:50PM GMT

What’s the best way to really screw up my transaction log files?



Posted by: Denny Cherry
Tags:
SQL Server
Transaction Log

Managing the space used by your SQL Server transaction logs is pretty easy.

If the database is in full recovery mode then do transaction log backups on a regular basis.  Typically every 15-60 minutes.

The second option is to put the database into Simple recovery mode.  If you need the ability to do point in time recovery then you can’t use simple recovery mode at all.

Now there is something that you shouldn’t ever do.  Truncating the log and shrinking the file is the first thing that shouldn’t ever be done (contrary to popular opinion).

If you are doing log backups, and your database log file is growing to a large size, then you need to get more space for your disk.  Shrinking the log won’t do anything for you as the SQL Server will simply grow the file back out to a larger size.  All you are doing is giving yourself a false sense of security when it comes to how much free drive space that you have.

There are also some internal technical reasons to not do this as well which you can read a little about on Paul Randal’s blog.  (If Paul tells me he’s got a more specific post I’ll post that link as well.)

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: