I have 1GB databases that grow several GB log files. How do you safely clear those logs? Well, probably the best practice includes backing them up. That was overkill for me, and sifting through all the documentation and forums (eventually) led me to this simple pair of statements that do the trick immediately:
backup log [dbname] with truncate_only go DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY) go
Update: Mathias added a comment that deserves calling out:
Warning: Clearing the transaction log with TRUNCATE_ONLY will break your backup chain needed for FULL recovery. A FULL database backup should be executes right after truncating the logs. More info.
with 400 users and a report dept that suddenly started "trying things out", I had a log file almost as large as the VM I was running in.
ReplyDeleteThanks for your blog entry ! You're a lifesaver !
Great code. Thanks. You saved me 50 GB of my server's hard drive.
ReplyDeleteThank for sharing this SQL. I've been looking for this for a very long time.
ReplyDeletethanx a lot dude
ReplyDeleteIt's my turn to say : "Thanks, man!" :)
ReplyDeleteawesome, finally someone with the answer i'm looking for...
ReplyDeleteso simple so great! bro you saved my life! :D
ReplyDeleteWonderful, I'm so happy :)
ReplyDeleteGOD BLESS YOU, DUDE !
ReplyDeleteIt's so nice to see the .ldf file just 1.25 MB ! :) thx a lot
thanks a Lot Man, I am really happy, trying to email the database since long, but fail due to file size.
ReplyDeleteMuhammad Sharjeel Ahsan
Thanks a lot!!!
ReplyDeletethanks a lot man
ReplyDeleteํYou solve my problem.
ReplyDeleteThank you very much.
Does this work on SQL 2008?
ReplyDeleteI tried it and got the error:
Msg 155, Level 15, State 1, Line 1
'truncate_only' is not a recognized BACKUP option.
Thanks a lot !!!!
ReplyDeleteThanks Much - This info helped greatly
ReplyDeleteI Like :)
ReplyDeleteThank you :)
ReplyDeleteWarning: Clearing the transaction log with TRUNCATE_ONLY will break your backup chain needed for FULL recovery. A FULL database backup should be executes right after truncating the logs.
ReplyDeleteSource: http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/9997e75e-f126-4901-9379-de540a708ec9#352c6a1d-1866-4737-ada4-7eeff91cbcda
Thank you!
ReplyDeleteYou've saved the world !
ReplyDeleteThanks !
PS. This does not work on 2008
hi i tried this in sql server 2008 enterpirse
ReplyDeletefollowing error got
Msg 155, Level 15, State 1, Line 2
'truncate_only' is not a recognized BACKUP option.
mine too 'truncate_only' is not a recognized BACKUP option.
DeleteInteresting post… good info, This is one example of why I get frustrated with SQL Server. I have found another helpful post see here: http://www.sqlserverlogexplorer.com/how-to-clear-transaction-with-dbcc-shrinkfile/
ReplyDeleteThis is Awsome!!!! Thank you so much! You safe my HDD!!
ReplyDelete