Thursday, 2 August 2012

SQL database Incremental Shrink TSQL

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.
at

2 comments:

  1. 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

    declare @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)

    ReplyDelete

  2. 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....
    5TB 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.

    ReplyDelete

Subscribe to: Post Comments (Atom)