Shrink the Unshrinkable SQL Transaction Log
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:
-
DBCC SHRINKFILE("MyDatabase_Log", 1)
-
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
-
DBCC SHRINKFILE("MyDatabase_Log", 1)
This freed up dozens of gigabytes on our server.

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!
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..
Thanks!!
April 3rd, 2009 at 7:29 pm
Juls, I corrected your original comment and deleted the comment pointing out the correction. Thanks.
May 12th, 2009 at 3:43 pm
You saved my life - thanks!
July 20th, 2009 at 3:10 pm
Dude, you are a genius. Thank you, thank you, thank you so much.
August 14th, 2009 at 11:41 am
THANK YOU SOOOOO MUCH!!!
August 20th, 2009 at 1:06 pm
Thanks! I just did this to a 80gb log file! everything’s back to normal now
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
November 6th, 2009 at 9:00 am
Thank you - that’s brilliant.