Friday, October 29, 2010

Shrinking Datafile in steps

Shrinking a database is only desirable in migration operations e.g. restoring to an underpowered development server. If you find yourself doing it, the following link is rather useful.

The script repeatedly shrinks a data file in small increments.
This avoids long file operations and makes it easy to stop at any time. : Shrink DB File by Increment to Target Free Space

From the script, this piece of sql is useful to show Data File Usage.

 [FileSizeMB] =
 [UsedSpaceMB] =
  convert(numeric(10,2),round(fileproperty(,'SpaceUsed')/128.,2)) ,
 [UnusedSpaceMB] =
  convert(numeric(10,2),round((a.size-fileproperty(,'SpaceUsed'))/128.,2)) ,
 [DBFileName] =
 sysfiles a

link : dba 101 : shrinkfile

No comments: