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.

1:  set nocount on  
2:   declare @DBFileName sysname  
3:  declare @TargetFreeMB int  
4:  declare @ShrinkIncrementMBint  
5:  -- Set Name of Database file to shrink  
6:  set @DBFileName = ''  
7:  -- Set Desired file free space in MB after shrink  
8:  set @TargetFreeMB = 0  
9:  -- Set Increment to shrink file by in MB  
10:  set @ShrinkIncrementMB= 100  
11:  -- Show Size, Space Used, Unused Space, and Name of all database files  
12:  select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)),  
13:  [UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,  
14:  [UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,[DBFileName]= a.name from sysfiles a  
15:  declare @sql varchar(8000)  
16:   declare @SizeMB float  
17:  declare @UsedMB float  
18:   -- Get current file size in MB  
19:  select @SizeMB = size/128. from sysfiles where name = @DBFileName  
20:  -- Get current space used in MB  
21:  select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0  
22:  -- Loop until file at desired size  
23:  while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB  
24:  begin  
25:  set @sql = 'dbcc shrinkfile ( '+@DBFileName+', ' + convert(varchar(20),convert(numeric(10,0),@SizeMB-@ShrinkIncrementMB))+' ) WITH NO_INFOMSGS'  
26:  print 'Start ' + @sql + ' at ' + convert(varchar(30),getdate(),121)  
27:   exec ( @sql )  
28:  print 'Done ' + @sql + ' at '+convert(varchar(30),getdate(),121)  
29:  -- Get current file size in MB  
30:   select @SizeMB = size/128. from sysfiles where name = @DBFileName  
31:  -- Get current space used in MB  
32:  select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0  
33:  print 'SizeMB=' + convert(varchar(20),@SizeMB) + ' UsedMB=' + convert(varchar(20),@UsedMB)  
34:  end  
35:  select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName  
Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

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