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

Published On: 2010-06-09By:

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

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via