Bdtjtk

I am working on a database that has been restored from a backup. It is then getting a large portion of the data deleted from it so that we can have a smaller subset of the data for dev and testing efforts. For example, we are deleting old orders.

All of this results in a database that was about 2 TB only having 100 GB of data in it. So I need to shrink it. The problem is that the script I was given by the previous dev is taking longer to run than the entire rest of the restore, and data reduction process.

The previous dev gave me the below script that loops over and reduces by a portion of the needed amount each time using DBCC SHRINKFILE. This is what we are using right now but I am sure this is not the best solution as it takes a really long time and I'm sure the amount it is fragmenting things is horrifying.

If it is any help we are using both Redgate and SQL Server to create backups that the developers can use to create their working databases.

DECLARE @FileSpace int;

DECLARE @MinSize int

DECLARE @Size int;

DECLARE @FileName nvarchar(100);



SET @Size = @FileSpace;



WHILE @Size > @MinSize

BEGIN

    SET @Size = @Size - 1000;



    DBCC SHRINKFILE (@FileName , @Size)



    SET @Size = (select database_files.size/128 from sys.database_files where database_files.name = @FileName);

END

I'm really needing something that will be much faster because at present it has taken well over 3 days.

First

Why you should NOT be shrinking alot. with emphasis on the included links in that article.

And then there is this parameter

As written this would never execute since @MinSize is null @Size > null returns null and thus is false, preventing the execution. If you are setting this to something, like a low number, you could have an infinite loop if the condition @Size > @MinSize is never met.

But it's not that... so now what

Another culprit could be blocking. Yep, DBCC SHRINKFILE can cause blocking. To see if this is true, I'd start with Adam Machanic's sp_WhoIsActive. It's well documented and will allow you to do get the blocking tree via @find_block_leaders parameter.

It's not fast, usually

As many articles have illustrated, SHRINKFILE eats up CPU and kills your I/O, can cause blocking, produces internal fragmentation which can leave you back to the same spot you are in after fixing that via, hopefully Ola's Index Optimize scripts.. So maybe your server is just under spec'd. This is the least likely but is always a potential.

What would you do if you were me?

Back the database up, and then restore it over your current version. The backup processes doesn't backup empty pages. Hopefully your fill factor is set to 100% and this isn't a bloating issue. See more on that here. So a quick backup and restore would fix your issue.

If I really needed to shrink a data file, I wouldn't use a looping script. I'd do them one at a time. You can even use the GUI! At a minimum, add some PRINT commands to your script to see what could be causing the infinite loop, if it is in fact infinite. I'd print out all of the parameter values each iteration to start with.