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.
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???
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.
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
November 18th, 2010 at 2:09 am
[...] Found a solution. [...]
January 17th, 2011 at 2:32 pm
Brilliant. Bacon saved, thanks.
January 18th, 2011 at 10:16 am
Thanks! worked
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.
September 14th, 2011 at 12:37 am
” perfect solution ”
thanx a lot……..
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!
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