Truncating Transaction Logs in SQL 2008
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