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

One Response to Shrinking all online Database Files

  1. […] the database log files of are clogging my harddisks again, I shrink them using a simple script. (Yes, I know you usually don’t need to do this, but on a dev machine harddisk space can […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: