Shrinking all online Database Files

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

Advertisements