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