Friday 29 October 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.

SQLTeam.com : Shrink DB File by Increment to Target Free Space

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

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

link : dba 101 : shrinkfile

No comments: