Monday, 16 December 2013

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