Truncating Transaction Logs in SQL 2008

Posted on 29. Jan, 2010 by 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
Bookmark and Share

8 Responses to “Truncating Transaction Logs in SQL 2008”

  1. Zehra Nasif

    27. Jan, 2011

    Thank you so much; saved a lot of time and headache when I needed this script right away.

    Reply to this comment

  2. Bernhard Marx

    12. Mar, 2011

    Thanks a lot. This saved my “life”

    Reply to this comment

  3. Dileep

    22. Jun, 2011

    That was seriously good.

    Reply to this comment

  4. juanMa

    24. Jun, 2011

    Have you tried using the built in function quotename() for T-SQL?
    It returns the input string a valid SQL Server delimited identifier. ie, with [ ]

    Very useful script.

    Reply to this comment

  5. juanMa

    24. Jun, 2011

    Hey Bryan, here is my version I was working on when I stumbled upon your version (wasn’t sure how to get the log names). It’s made for sql server 2005. Also I don’t like cursors so they’re not here.
    ——————————————
    declare @SQLString NVARCHAR(MAX), @dbName as nvarchar(255), @logName as nvarchar(255)
    declare @dbList as table( dbName nvarchar(255),logName nvarchar(255), runStatus bit default 0)

    –Populate table variable with all db names and all db log file names
    insert into @dbList (dbName,logName)
    SELECT a.name, b.name from sys.databases a join sys.master_files b on a.database_id = b.database_id
    where b.type = 1 AND a.name not in (‘master’, ‘model’, ‘msdb’, ‘tempdb’,'distribution’)
    order by a.name
    –Loop over all db
    while (exists(select * from @dbList where runStatus=0))
    begin
    –Get first available database
    SELECT top 1 @dbName = dbName, @logName = logName from @dbList where runStatus = 0
    –Prepare sql statement here
    SET @SQLString =
    N’ USE ‘ + quotename(@dbName) + N’ ; ‘ +
    N’ DBCC SHRINKFILE(‘ + quotename(@logName) + ‘ , 1) ; ‘ +
    N’ BACKUP LOG ‘ + quotename(@dbName) +’ WITH TRUNCATE_ONLY ;’ +
    N’ DBCC SHRINKFILE(‘ + quotename(@logName) + ‘ , 1) ;’
    –Execute
    EXECUTE sp_executesql @SQLString
    –Flag used database
    update @dbList set runStatus = 1 where dbName = @dbName
    end

    Reply to this comment

  6. Pare

    28. Aug, 2011

    Great Man, thx

    Reply to this comment

  7. MM

    08. Sep, 2011

    Thank you so much………
    you made my day…

    Reply to this comment

Leave a Reply