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

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

Share

Share on facebook
Share on twitter
Share on linkedin

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?