Shrinking all log files

18. August 2008 by Bennett.

I’m not a big proponent of shrinking files. There are a lot of postings on DBA forums citing why this practice should be avoided. However we have a development server with a lot of databases, and the log files need to be shrunk on a pretty regular basis.

I wrote a little query to dynamically build the list of databases and logical filenames and then run DBCC SHRINKFILE to shrink all the log files. Here’s the script:

SET nocount ON;
CREATE TABLE #tempFileInfo
(
  dbName VARCHAR(256),
  logicalName VARCHAR(256),
)

DECLARE @DBName VARCHAR(256)
DECLARE @SQL VARCHAR(MAX)

DECLARE CursorDB CURSOR FOR
SELECT [name] FROM sys.databases WHERE database_id > 4 AND state_desc = ‘ONLINE’
OPEN CursorDB

FETCH NEXT FROM CursorDB INTO @DBName
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @DBName = ‘[’ + @DBName + ‘]’
    SET @SQL = ’select ‘ + CHAR(39) + @DBName + CHAR(39) + ‘, [name] from ‘ + @DBName + ‘.sys.sysfiles WHERE filename LIKE ‘+ CHAR(39) + ‘%ldf’ +CHAR(39)
    –print @SQL
    INSERT INTO #tempFileInfo
    EXEC (@SQL)
    FETCH NEXT FROM CursorDB INTO @DBName
  END
CLOSE CursorDB
DEALLOCATE CursorDB

SET @SQL = ”
SELECT @SQL=COALESCE(@SQL,”)+ ‘USE ‘ + dbName + ‘ DBCC SHRINKFILE(’ + CHAR(39) + logicalName + CHAR(39) + ‘, 1); ‘
FROM #tempFileInfo

PRINT @SQL
EXEC(@SQL)

DROP TABLE #tempFileInfo

Posted in T-SQL, SQL Server | Print | No Comments »