Shrinking your Transaction Log with SQL Server 2008 to Free Disk Space

If you have been used to clearing up disk space on your development and test SQL server instance file systems with SQL Server 2000 or SQL Server 2005 by using the famous TRUNCATEONLY option on your transaction logs, you might be disheartened that they have removed this option from SQL Server 2008 (with good reason some might say).

If you do try to use it, you will get the infamous:

‘TRUNCATEONLY’ is not a recognized BACKUP option.

To achieve the same effect with SQL Server 2008, you can toggle the recovery mode for the target database and then call your DBCC SHRINKFILE to clear up disk space hogged by your transaction log.

 
USE MyDatabase
GO
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE('MyDatabase_log',1)
GO
ALTER DATABASE MyDatabase SET RECOVERY FULL
GO

(Of course if you are already using simple recovery, you wouldn’t need to toggle.)

Hope this helps!

This entry was posted in MS SQL Server, T-SQL. Bookmark the permalink.

One Response to Shrinking your Transaction Log with SQL Server 2008 to Free Disk Space

  1. siva says:

    Thanks alot. It got resolved with the porvided solution

Leave a Reply

Your email address will not be published. Required fields are marked *