To Shrink, Or not to Shrink, that is the question

Whether 'tis nobler in the log to suffer
The growth and space of outrageous fortune - bla bla

You see many a post debating this issue.  On the one hand you have the newer DBA's that will shrink the data file at every opportunity to try to regain space on the disk.  Only for it to grow again the next day.

Or others that will shrink the data file and then do a reindex, and make it grow again.

Its my opinion that for every day usage, the data and log file should be left alone.  To ensure that databases can be restored in a disaster, you should be taking regular backups, this would included fulls, diffs, and transnational.

 These transnational backups should keep the database log from growing too high, by allowing the space to be reused.

However ..... There are times when the data file does need shrinking.  When?

Lets say you have a very large database that get unmanageable, you might want to split that up into smaller yearly (for example) databases.  You would then need top reclaim that space at the end.

So how should this be done.  Below is a helpful script that will shrink the file stage by stage, to avoid putting a disk performance issue on the database.

USE <DB Name>

GO

DECLARE  @vLogicalFilename VARCHAR(250) 

DECLARE  @vTargetSizeinMB INT 

DECLARE  @vCurrentSizeinMB INT 

DECLARE  @vDecrementinMB INT 

DECLARE  @vSQL VARCHAR(250) 

SET @vLogicalFileName = 'DataFileName' 

/* Please make sure that the Target size is achievable  

   otherwise the script will go in an infinite loop    */ 

SET @vTargetSizeinMB = 21000 

SET @vDecrementinMB = 200 /* Don't set this value to more than 2000 */ 

IF EXISTS (SELECT * 

           FROM   sysfiles 

           WHERE  name = @vLogicalFileName) 

  BEGIN 

    SELECT @vCurrentSizeinMB = size / 128 

    FROM   sysfiles 

    WHERE  name = @vLogicalFileName 

     

    WHILE (@vTargetSizeinMB <= @vCurrentSizeinMB - @vDecrementinMB) 

      BEGIN 

        SELECT @vSQL = 'DBCC SHRINKFILE(' + @vLogicalFileName + ',' + Cast((@vCurrentSizeinMB - @vDecrementinMB) AS VARCHAR(10)) + ')' 

         

        SELECT @vSQL 

         

        EXEC( @vSQL) 

         

        SELECT @vCurrentSizeinMB = size / 128 

        FROM   sysfiles 

        WHERE  name = @vLogicalFileName 

      END 

  END