SQL 2008 Truncate log files for all databases

First, let me qualify this post. This is not my code. I modified the code to fit my needs. The original code and article are here.

My modification simply allows the script to bypass any databases that are offline. We occasionally will take a database offline and leave it that way for a few weeks. By simply adding the statement “AND DATABASEPROPERTYEX(name, ‘status’) = ‘ONLINE'” to the original code, I was able to accomplish this, which made the next step even easier, automating this script to run once a week.

All of this came about because we were unable to get this to run reliably as part of a Maintenance Plan. By implementing this script as a SQL Agent Job, separate from our maintenance plans, we were able to get control of unwieldy log files.

Many of you may find this useful, especially on a server where databases are being created and deleted on an almost daily basis.

for an explanation of the base code, without my modifications, read this.

DROP TABLE #TransactionLogFiles
CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) )
-- step 1. get hold of the entire database names from the database server

DECLARE DataBaseList CURSOR FOR 
SELECT name FROM SYS.sysdatabases

WHERE NAME NOT IN ('master','tempdb','model','msdb','distribution')
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

DECLARE @DataBase VARCHAR(128)
DECLARE @SqlScript VARCHAR(MAX) 
-- step 2. insert all the database name and corresponding log files' names into the temp table
OPEN DataBaseList FETCH
NEXT FROM DataBaseList INTO @DataBase
WHILE @@FETCH_STATUS <> -1 
BEGIN

SET @SqlScript = 'USE [' + @DataBase + '] INSERT INTO #TransactionLogFiles(DatabaseName, LogFileName) SELECT '''
+ @DataBase + ''', Name FROM sysfiles WHERE FileID=2'
--SELECT @SqlScript
EXEC(@SqlScript) 
FETCH NEXT FROM DataBaseList INTO @DataBase END

DEALLOCATE DataBaseList

-- step 3. go through the each row and execute the shrinkfile script against each database log file on the server
DECLARE TransactionLogList CURSOR FOR 
SELECT DatabaseName, LogFileName FROM #TransactionLogFiles 
DECLARE @LogFile VARCHAR(128) 

OPEN TransactionLogList FETCH
NEXT FROM TransactionLogList INTO @DataBase, @LogFile
WHILE @@FETCH_STATUS <> -1 
BEGIN 
SELECT @SqlScript = 'USE [' + @DataBase + '] '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY SIMPLE WITH NO_WAIT '
+ 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY FULL WITH NO_WAIT'
EXEC(@SqlScript) 

FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END
DEALLOCATE TransactionLogList
--SELECT * FROM #TransactionLogFiles

-- step 4. clean up
DROP TABLE #TransactionLogFiles