Shrinking SQL Server Databases in batch

Before I begin yes, yes I know that shrinking database mdf files should be avoided. If you don’t believe me check out this post from people in the know like Brent Ozar / Paul Randal and Pinal Dave.

Lets say that you have one of those rare scenarios where you have a large database say 1Tb and you have archived or trimmed back as the result of a change in a data retention policy to say 500Gb.

You now have 500Gb that can be reclaimed (or more if its part of an availability group). The first thing would be to try and simply truncate the empty space and the end of the file.

DBCC SHRINKDATABASE(YourDBName, TRUNCATEONLY)

now if you have a lot of fragmentation or pages are at the end of the file this may not do anything at all.

So your now in a position where you have two options:

  1. Rebuild the clustered index try shrink again
  2. Shrink the files but specifying the specific file name

What if option 1 was a no go as rebuilding the clustered index would eat transaction log space that you perhaps didn’t have or you cannot get a sufficient maintenance window to cover the rebuild.

So its option 2 here is my advice, rather than trying to remove the 500Gb all at once break it down into manageable chunks. Here is my script for batching shrinking in small chunks, you can adjust the size to shrink by and target size so it stops when you want it to.

Use myDatabaseName

declare @DBSizeMb as bigint
declare @ShrinkbyMb as int = 100
Declare @DatabaseFile as varchar(200) = ‘myDatabase_FileName’
Declare @TargetShrinkSizeMb as bigint = 500000
SELECT
@DBSizeMb = (s.size * CONVERT(float,8))/1024
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)

while @DbSizeMB > @TargetShrinkSizeMb

Begin

Print @DbSizeMb – @ShrinkbyMb

declare @execstatement as varchar(200)
set @execstatement = ‘DBCC SHRINKFILE (N”’ + @DatabaseFile +”’ ,’ + cast( (@DbSizeMb – @ShrinkbyMb) as varchar(100)) +’);’

print @ExecStatement
print Getdate()
EXEC( @ExecStatement)

WAITFOR DELAY ’00:00:10′;

SELECT
@DBSizeMb = (s.size * CONVERT(float,8))/1024
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
End

After you have shrunk the data file and released the space back to the drive your indexes will be heavily fragmented so make sure you rebuild them, nice and fresh!

I accept no responsibility for this code, you run it at your own risk. Tested in SQL 2012 SP2, please comment for bugs / improvements.

Advertisements
Shrinking SQL Server Databases in batch