Of Ones and Zeros

Truncating Transaction Logs in SQL 2008

Posted on 29. Jan, 2010 by bryan in SQL

One of my clients is beginning to migrate to SQL 2008 for their SharePoint farms.  In the past, we have used scripts to truncate the transaction logs and shrink the DBs in the non-production environments using the “BACKUP LOG [dbname] WITH TRUNCATE_ONLY” command.  As you may be aware, the “WITH TRUNCATE_ONLY” option has been deprecated in SQL 2008.  In SQL 2008 instead you need to change the recovery mode of the database to SIMPLE and then back to FULL in order to truncate the log (or perform a backup of the log of course).  In production environments obviously backing up the transaction log often is the best strategy, however in non-production environments that isn’t always feasible.  I wrote the following script for this client to truncate the log all databases other than the system ones. 

Enjoy!

DECLARE @DatabaseNames TABLE
(
	name varchar(255)
)

INSERT INTO @DatabaseNames
SELECT name FROM sys.databases WHERE
name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

DECLARE DatabaseCursor CURSOR FOR
SELECT * FROM @DatabaseNames

OPEN DatabaseCursor

DECLARE @DatabaseName varchar(255)
DECLARE @LogFileName varchar(255)
DECLARE @SqlStatement varchar(2000)

FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS=0
BEGIN
	PRINT '----------------------------------------------------------'
	PRINT 'Processing database ' + @DatabaseName
	PRINT '----------------------------------------------------------'

	SET @SqlStatement = 'ALTER DATABASE [' + @DatabaseName +
		'] SET RECOVERY SIMPLE'

	PRINT @SqlStatement
	EXEC (@SqlStatement)

	SET @LogFileName =
		(
			SELECT b.name
			FROM sys.databases a
			INNER JOIN sys.master_files b ON
				a.database_id = b.database_id
			WHERE
				a.name = @DatabaseName
				AND b.name LIKE '%log')

	PRINT @LogFileName

	SET @SqlStatement = 'USE [' + @DatabaseName +
		'] DBCC SHRINKFILE(N''' + @LogFileName + ''', 1)'

	PRINT @SqlStatement
	EXEC (@SqlStatement)

	SET @SqlStatement = 'ALTER DATABASE [' + @DatabaseName +
		'] SET RECOVERY FULL'

	PRINT @SqlStatement
	EXEC (@SqlStatement)

	FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor