Monday, July 13, 2015

DB Shrinkfile to Minimum Script

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,
       'DBCC SHRINKFILE (N' + '''' + a.name + '''' + ', ' + cast(fileproperty( a.name,'SpaceUsed')/128 + 256 as nvarchar(500)) + ')'
from
sysfiles a