Shrinking Excess Free Space From the Database

The script below will scan through all files in all the databases on a given instance and identify the files with the most free space.  It will shrink that file down in a small increment and then identify the next file with the most free space to shrink (might be that same file still).

I use this script after a big release to identify excess free space and remove it.

It also comes in hand when a drive is out of space and you need to clean it up fast.

SET NOCOUNT ON

DECLARE @dbname sysname

DECLARE @stmt varchar(1000)

DECLARE @shrink_increment integer

DECLARE @volume char(1)

DECLARE @space_to_free integer

DECLARE @FileID integer

DECLARE @TotalMB integer

SET @shrink_increment = 250 --Increment which you would like to shrink files in.

SET @space_to_free = 100000 --Total amount of space you want to free.

SET @volume = null --If you would like to shrink a particular drive just enter it here.  If you want to do all drives set it to null.

IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name like '##data%')

      DROP TABLE ##data

CREATE TABLE ##data (

      Fileid int NOT NULL,

      FileGroup int NOT NULL,

      TotalExtents int NOT NULL,

      UsedExtents int NOT NULL,

      Name sysname NOT NULL,

      FileName varchar(300) NOT NULL,

      DatabaseName sysname NULL,

      TotalMB integer NULL,

      UsedMB integer NULL)

DECLARE db_curs CURSOR FOR

SELECT name

FROM master.sys.databases

WHERE state = 0 --Only get ONLINE databases

and database_id > 4

OPEN db_curs

FETCH NEXT FROM db_curs INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

      SET @stmt = 'USE ' + @dbname + char(10) + 'DBCC showfilestats with no_infomsgs'

      INSERT INTO ##data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName)

      EXECUTE(@stmt)

      UPDATE ##data

      SET DatabaseName = @dbname

      WHERE DatabaseName is null

      SET @stmt = 'USE ' + @dbname + char(10) + 'dbcc sqlperf(logspace) with no_infomsgs'

      FETCH NEXT FROM db_curs INTO @dbname     

END

CLOSE db_curs

DEALLOCATE db_curs

UPDATE ##data

SET TotalMB = (TotalExtents * 64.0)/ 1024.0,

UsedMB = (UsedExtents * 64.0)/ 1024.0

WHILE @space_to_free > 0

BEGIN

      SELECT TOP 1

            @FileID = FileID,

            @TotalMB = TotalMB - @shrink_increment,

            @dbname = DatabaseName

      FROM ##data

      WHERE substring(FileName, 1, 1) = @volume or @volume is null

      and totalmb > 1000

      ORDER BY (cast(UsedMB as float)/cast(TotalMB as float))

     

      SET @stmt = 'USE ' + @dbname + char(10) + 'DBCC SHRINKFILE(' + cast(@FileID as varchar) + ', ' + cast(@TotalMB as varchar) + ')'

      PRINT @stmt

      execute(@stmt)

      Update ##data

      SET TotalMB = @TotalMB

      WHERE FileID = @FileID

      and DatabaseName = @dbname

      SET @space_to_free = @space_to_free - @shrink_increment

END