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. Tibor Karzai in particular has an article on why this is a bad idea. Shrinking log files in particular is problematic as log files cannot leverage instant file initialization; they have to be zeroed as they are grown.
Having said that, we have a development server with a lot of databases and no budget for storage upgrades. Consequently someghing has to give – and so we shrink the log files 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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 |
