How to Truncate Your Database Transaction Log

Posted Sunday, November 30, 2008 8:29 PM by Nathan Zaugg

Sometimes those transaction logs get a little large and unwieldy!  For dev machines I almost never want the transaction log to ever be larger than 1GB.  I often find some transaction logs of 10GB and larger and there is little in the way of GUI tools to help you free up that wasted space.  Luckly there is a really good command for SQL Server 2005 & SQL Server 2008 that will allow you to truncate the existing transaction log.


That's it!  You would think they would include a button or something in SQL Server Management Studio, but as I understand it this would be a bad thing to do in a production environment.

Hope this helps someone -- if so leave a comment!


If you are doing this is SQL Server 2008 then it no longer recognises the DUMP command.  The best way to do this going forward is:


The problem with this approach is that it will change your recovery mode from whatever it was to 'SIMPLE'. You will need to chage the recovery mode back after you run the command.