When we shrink a SQL database through GUI we usually find the database never releases all the free space, It is always a good practice particularly in Production OLTP systems to shrink the database in small chunks so the data databse pages can be re-arranged and can free all unused space. The below TSQL will allow shrink a database file in increments until it reaches a target free space limit.
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 declare @DBFileName sysname declare @TargetFreeMB int declare @ShrinkIncrementMBint -- Set Name of Database file to shrink -- Set Desired file free space in MB after shrink -- Set Increment to shrink file by in MB set @ShrinkIncrementMB= 100 -- 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)) , declare @sql varchar(8000) -- 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.0 -- Loop until file at desired size while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB set @sql = 'dbcc shrinkfile ( '+@DBFileName+', ' + convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) WITH NO_INFOMSGS' print 'Start ' + @sql + ' at ' + convert(varchar(30),getdate(),121) print 'Done ' + @sql + ' 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.0 print 'SizeMB=' + convert(varchar(20),@SizeMB) + ' UsedMB=' + convert(varchar(20),@UsedMB) select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName