In a previous post I described how to shrink a MSSQL log file of a specific database from the SQL prompt.
We can extend this example to iterate through all accessible databases (i.e. attached, online and not readonly) and execute the SHRINKFILE command on each file in these databases.
First we collect all databases and their files:
SET NOCOUNT ON DECLARE @db NVARCHAR(128), @s NVARCHAR(128) CREATE TABLE #files (db NVARCHAR(128), name NVARCHAR(128)) DECLARE c CURSOR FOR SELECT name FROM master..sysdatabases WHERE DATABASEPROPERTY(name, 'IsOffline') = 0 AND DATABASEPROPERTY(name, 'IsReadOnly') = 0 AND DATABASEPROPERTY(name, 'IsDetached') = 0 AND DATALENGTH(sid) > 1 ORDER BY name OPEN c FETCH c INTO @db WHILE @@FETCH_STATUS = 0 BEGIN PRINT N'scanning ' + @db SET @s = N'USE ' + @db + N' SELECT ''' + @db + ''', RTRIM(name) FROM sysfiles' INSERT INTO #files EXEC (@s) FETCH c INTO @db END CLOSE c DEALLOCATE c
Table #files now contains the names of the databases and their files.
Next we need to issue a USE database plus the DBCC SHRINKFILE commands:
DECLARE c CURSOR FOR SELECT db, name FROM #files ORDER BY db, name OPEN c FETCH c INTO @db, @s WHILE @@FETCH_STATUS = 0 BEGIN SET @s = N' USE ' + @db + N' dbcc shrinkfile (' + @s + N', truncateonly)' PRINT @s EXEC (@s) FETCH c INTO @db, @s END CLOSE c DEALLOCATE c DROP TABLE #files
Pingback: Problems Shrinking SQL Server Log Files | devioblog