« Combining Repositories Into One Large Repository Avoiding the Password Prompt for SSH , »

Shrink the Unshrinkable SQL Transaction Log

August 4th, 2008 by Josh How To, SQL

Various reasons may cause SQL Server to get in a rut and not empty the transaction log of a database. In my case, our database backups were failing without our knowledge for several weeks, so the backups were never successful, and the transaction logs of a few databases grew so large that the backup process would still not clear out the transaction log. In one case, we had a 187MB database with a 37GB transaction log!

The insanity had to stop! A handful of databases like this would put us over the top on that particular server's hard drive storage.

The SQL Server GUI for shrinking the database rendered no effect, and even using the DBCC SHRINKFILE command was not working.

The key, as explained by Pinal Dave, is to run the SHRINKFILE command twice, with an explicit backup log truncation in between both runs. This code here will get you up and running:

PLAIN TEXT
SQL:
  1. DBCC SHRINKFILE("MyDatabase_Log", 1)
  2. BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
  3. DBCC SHRINKFILE("MyDatabase_Log", 1)

This freed up dozens of gigabytes on our server.

9 Responses to “Shrink the Unshrinkable SQL Transaction Log”

  1. Buba Rooni Says:
    November 11th, 2008 at 3:38 pm

    Perfect! I’ve had a db that wouldn’t post any transaction the last few days, nothing I tried would shrink it and I had management breathing fire down my neck.

    Your script did the trick!

    Thanks!

  2. Juls Briosos Says:
    April 2nd, 2009 at 10:57 pm

    we were having this same problem today and this same solution did the trick! we were breaking our necks trying to shrink the database logs to no avail… I sure wish we saw this post a little earlier.. :P Thanks!!

  3. Administrator Says:
    April 3rd, 2009 at 7:29 pm

    Juls, I corrected your original comment and deleted the comment pointing out the correction. Thanks.

  4. Praesagus Says:
    May 12th, 2009 at 3:43 pm

    You saved my life - thanks!

  5. rob Says:
    July 20th, 2009 at 3:10 pm

    Dude, you are a genius. Thank you, thank you, thank you so much.

  6. Rasesh Says:
    August 14th, 2009 at 11:41 am

    THANK YOU SOOOOO MUCH!!!

  7. dude Says:
    August 20th, 2009 at 1:06 pm

    Thanks! I just did this to a 80gb log file! everything’s back to normal now :)

  8. Dallas Says:
    September 9th, 2009 at 9:21 pm

    truncate_only is no longer supported in sql server 2008.
    See:
    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d0958b81-8cfb-4b2b-8b5a-8e50c835f920

  9. Liz Says:
    November 6th, 2009 at 9:00 am

    Thank you - that’s brilliant.

Leave a Reply

  Theme Brought to you by Directory Journal and Elegant Directory.