This script is used to shrink a database file in increments until it reaches a target free space limit.
Run this script in the database with the file to be shrunk.
Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
I made a small amendment on this script that has worked nicely. It allows you to set the percentage of free space you want left after the shrink
ReplyDeletedeclare @DBFileName sysname
declare @percentage decimal
declare @TargetFreeMB int
-- Set Name of Database file to shrink
set @DBFileName = 'databasename'
-- Set FreeSpace Multipler
set @percentage = 21
-- Set Desired file free space in MB after shrink
set @TargetFreeMB=(select convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2))*(1+(@percentage/100)) from sysfiles a where a.name=@DBFileName)
Thank you very much for this, I had to add a Convert(BIGINT,...) around the @SizeMB-@ShrinkIncrementMB in order to get this to work but it is saving my bacon as I type this....
ReplyDelete5TB file which after compressing some tables will end up ~1.5 TB... need to get the file size down so I can move it to smaller SAN Volume.