Microsoft SQL Server 2008 – Tip 1 – Purge Transaction Log

We’ve been migrating some of our production databases at work to SQL 2008 and I’m finding that Microsoft changed a lot. They updated the BACKUP LOG t-sql command and removed the TRUNCATE_ONLY switch, which means quickly and easily purging the transaction log requires a bit more work. I spent a little while yesterday and wrote the following T-SQL to purge the logs.


------------------------------------------------------------------------------
-- Otto R. Radke - http://ottoradke.com
-- Info: T-SQL script to shrink a database's transaction log. Just set the
-- database name below and run the script and it will shrink the
-- transaction log.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Update the line below with the name of the database who's transaction
-- log you want to shrink.
------------------------------------------------------------------------------
USE YourDatabaseName
------------------------------------------------------------------------------
-- Don't change anything below this line.
------------------------------------------------------------------------------
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname
-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )
-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)
-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )
-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )
------------------------------------------------------------------------------


If you want a copy of the source, let me know and I’ll either update this post with the source or email it to you.

Tagged as: microsoft, sql-2008, tsql