« , »

Shrink the Unshrinkable SQL Transaction Log

August 4th, 2008 by Josh How To, SQL19 Comments

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.

19 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.

  10. Jason Says:
    April 19th, 2010 at 10:33 am

    Obviously SQL 2008 disabled TRUNCATE_ONLY.
    Their workaround is to set Recovery Mode to Simple, do the DBCC SHRINKFILE, then set it back to Full.
    This does not make SQL Server more secure at all – it just creates more work for the DBA.

    What I want to know is, how to shrink that 37GB T-log file WITHOUT taking the database out of Full Recovery mode, and WITHOUT jeopardizing point-in-time restore.
    I have a 24/7 OLTP system with lots of transactions. The log file is going to grow and I don’t have 2 TB of fast disk space to reserve for the logs. So I need to reduce it without losing Point-In-Time recovery.

    Any thoughts???

  11. Sonet Says:
    June 10th, 2010 at 6:06 pm

    Why not simply backup the logs? TRUNCATE_ONLY has been removed for a reason. The problem with it is that it breaks the transaction log chain, which makes recovering to a point in time impossible. This should be avoided under all circumstances. Instead backup the database log:

    BACKUP LOG DatabaseName TO DISK = N’C:\Backup\DatabaseName_log.bak’
    GO

    If you are still running out of space, perform log backups more frequently.

  12. Dave Says:
    June 30th, 2010 at 8:12 am

    Good article on the issue of transaction log management:

    http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx

  13. SQL Server 2005 can’t increase log file size Drija Says:
    November 18th, 2010 at 2:09 am

    [...] Found a solution. [...]

  14. Jim Says:
    January 17th, 2011 at 2:32 pm

    Brilliant. Bacon saved, thanks.

  15. Tim Says:
    January 18th, 2011 at 10:16 am

    Thanks! worked

  16. Dean Says:
    July 11th, 2011 at 10:49 am

    Piecing the post and comments together, what worked for me in the end with SQL 2008 was the following:

    BACKUP LOG DatabaseName TO DISK = N’D:\Backup\DatabaseName_log.bak’
    GO
    DBCC SHRINKFILE(“MyDatabase_Log”, 1)
    GO

    Thanks guys.

  17. amith Says:
    September 14th, 2011 at 12:37 am

    ” perfect solution ”

    thanx a lot……..

  18. Mark Thornton Says:
    October 5th, 2011 at 5:31 am

    Brilliant – you not only confirmed my suspicions of a bug in SQL Server 2008, but also gave me a workaround.

    Many thanks!

  19. James Says:
    December 6th, 2011 at 1:20 pm

    Works nice,found it here too

    http://www.sqlserveroptimizer.com/2011/11/how-to-shrink-logfile-in-sql-server-20052008/

    thanks again

Leave a Reply

  Theme Brought to you by Directory Journal and Elegant Directory.