Shrinking Database Files With Excess Free Space

This script is intended for environments where space constraints require the space to be closely maintained. This is not a good script to run as part of a normal production maintenance plan, unless you adjust the variables to high enough levels that it only catches extreme cases. Shrinking a database takes a lot of time and CPU power and won’t help performance.

This script does include an index rebuild at the end as indexes become very fragmented after this process.

This is not thoroughly tested yet.

--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
    --AUTHOR:       BEN FARNSWORTH
    --DATE:     3/5/14  
    --DESC:     FINDS DATABASE FILES WITH EXCESS FREE SPACE AND SHRINKS FILE TO IDEAL SIZE
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
 
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
--==--==--==--==--== SET VARIABLES TO DETERMINE WHAT TO IGNORE =--==--==--==--==--==--==--==--==--==--==
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
DECLARE @freespaceThreshhold INT, @spaceUsedPercThreshhold FLOAT, @idealSizeAsPerctageOfSpaceUsed FLOAT;
 
    --  THESE VARIABLES WILL FILTER OUT DATABASES WHOSE FREE SPACE IS OK TO IGNORE
        --  IF FREE SPACE IS LESS THAN THIS, DO NOT BOTHER SHRINKING
    SET @freespaceThreshhold = 700  --MB
        --  
    SET @spaceUsedPercThreshhold = .15
     
     
    SET @idealSizeAsPerctageOfSpaceUsed = 1.14
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
 
 
SET NOCOUNT ON
 
IF OBJECT_ID('tempdb..#space') IS NOT NULL DROP TABLE #space;
IF OBJECT_ID('tempdb..#requirements') IS NOT NULL DROP TABLE #requirements;
 
CREATE TABLE #space(name varchar(100),filename varchar(200),filesizeMB decimal(12,2),spaceusedMB decimal(12,2),freespaceMB decimal(12,2));
CREATE TABLE #requirements(idealSize INT,dbName VARCHAR(100),[fileName] VARCHAR(100),processed BIT NOT NULL DEFAULT 0);
 
 
exec sp_msforeachdb @command1 = '
    use [?]
    INSERT INTO #space
    select
          name
        , filename
        , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
        , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
        , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a';
 
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
--==--==--==--==--== INSERT NEEDED DATA INTO #REQUIREMENTS TABLE -==--==--==--==--==--==--==--==--==--==
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
INSERT INTO #requirements( idealSize, dbName, [fileName], processed )
SELECT FLOOR((S.spaceusedMB * @idealSizeAsPerctageOfSpaceUsed)) AS [IdealSize], DB_NAME(MF.database_id), MF.name,0 FROM #SPACE S
INNER JOIN SYS.MASTER_FILES MF ON S.FILENAME = MF.PHYSICAL_NAME
WHERE type_desc = 'ROWS'
AND (s.freespaceMB/s.spaceUsedMB) > @spaceUsedPercThreshhold
AND freespaceMB > @freespaceThreshhold
AND MF.state_desc = 'ONLINE'
AND MF.database_id > 4
ORDER BY FREESPACEMB DESC;
 
 
 
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
--==--==--==--==--== LOOP THROUGH #REQUIREMENTS TABLE TO EXECUTE COMMANDS ==--==--==--==--==--==--==--==
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
WHILE (SELECT COUNT(*) FROM #requirements WHERE processed = 0) > 0
BEGIN
     
    --  Set variables
    DECLARE @db VARCHAR(100), @fileName VARCHAR(100), @idealSize INT, @shrink VARCHAR(200), @indexRebuild VARCHAR(200)
        SELECT TOP 1 @db = dbName, @idealSize = idealSize, @fileName = fileName FROM #requirements WHERE processed = 0;
         
        -- Shrink
        SET @shrink = 'USE [' + @db + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@idealSize AS VARCHAR(20)) + ');';
        PRINT @shrink;
        EXEC(@shrink);
         
        -- Rebuild indexes
        SET @indexRebuild = 'EXEC sp_msForEachTable @command1 = ''SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;''';
        PRINT @indexRebuild;
        EXEC(@indexRebuild);
        PRINT 'Done with ' + @db;
         
    --  Mark row complete
    UPDATE #requirements SET processed = 1 WHERE dbName = @db AND idealSize = @idealSize;
         
 
END