Gradual Database Shrink
Published: 31st May 2014
First things first… NEVER shrink your database unless you REALLY have to.

Now that’s out of the way, this is one of those occasions when I decided that a shrink was a bearable approach, however it wasn’t without issue and hence I found this funky piece of code.

Basically what had happened was that we had purged our databases of a LOT of old data, removed a good deal of tables that were no longer needed (basically they supported outdated functionality) and, as it turned out, reduced a 250GB database to just 35GB.

Obviousy there was the option to create a new filegroup and gradually rebuild the clustered indexes into that filegroup, but as this database was offline to users anyway, I decided to go with a shrink and leave the overnight maintenance to sort out the resulting fragmentation.

Sadly, and predictably with a shrink, things didn’t go according to plan.

The shrink simply ran and ran and ran until it eventually found it amusing to fail. Total space reclaimed? Zero.

At this point I should really have just gone back to the filegroup solution, but due to the strange failure I decided to persevere.

As it turns out, the key was to shrink the database in small chunks. This actually turned out to be very fast and worked very well indeed.

For the first time ever, I actually remembered where I found this script and therefore am happy to paste the original link as follows:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

The code in question is this:

-- Shrink_DB_File.sql
            /*
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 @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
*/
declare @DBFileName sysname
declare
@TargetFreeMB int
declare
@ShrinkIncrementMB int
    
-- Set Name of Database file to shrink
    
set @DBFileName = 'MyDatabaseFileName'
    
-- Set Desired file free space in MB after shrink
    
set @TargetFreeMB = 1000
    
-- Set Increment to shrink file by in MB
    
set @ShrinkIncrementMB = 50
    
-- Show Size, Space Used, Unused Space, and Name of all database files
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
declare @sql varchar(8000)
declare @SizeMB int
declare
@UsedMB int
    
-- Get current file size in MB
select @SizeMB = size/128.
    
from sysfiles
    
where name = @DBFileName
    
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
    
-- Loop until file at desired size
while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
    
begin
    set
@sql =
        
'dbcc shrinkfile ( '+@DBFileName+', '+
                
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
                
print 'Start ' + @sql
                
print 'at '+convert(varchar(30),getdate(),121)
    
exec ( @sql )
                
print 'Done ' + @sql
                
print 'at '+convert(varchar(30),getdate(),121)
        
-- Get current file size in MB
    
select @SizeMB = size/128.
        
from sysfiles
        
where name = @DBFileName
        
-- Get current space used in MB
    
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
    
select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
    
end
select
[EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
    
-- Show Size, Space Used, Unused Space, and Name of all database files
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


If you ever find yourself needing to shrink a datafile (please don’t find yourself in that position if you can help it) then maybe this code will help you as it helped me.
Comments:
NB: Comments will only appear once they have been moderated.