How to Truncate Your Database Transaction Log
Sunday, November 30, 2008 8:29 PM
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.
DUMP TRAN [DATABASE_NAME] WITH TRUNCATE_ONLY
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:
ALTER DATABASE [DATBASE] SET RECOVERY SIMPLE
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.