I am in the process of purging data from a multi-terabyte database which only has two data files on the PRIMARY file group.  I expect to free up at least 50% of space within the data files.  I need to reclaim that space and am looking for the most efficient way to do it.

The tables are a combination of heaps and clustered tables.  I am aware of the fragmentation issue by using the shrink command and will handle that separately if needed.  Unfortunately, I prefer not to create a secondary file group and move each table individually.  However, the shrink command either reorganizing or empty file options are running 16+ hours.

What is the recommended solution for this scenario?

• Edited by J I M B O Thursday, August 20, 2015 2:39 PM

In this situation, it was just faster to perform the shrink operation iteratively.  Trying to shrink by 100 GB in the production environment at times took over 7 hours.  However, shrinking by 10 GB took less than 5 minutes consistently.  I am not sure if it is the cloud configuration or how SQL Server coded SHRINKFILE, but I will take what I can at this point.

My solution was just to create a small script looping through the iterations using a WHILE statement.

Thanks for all your suggestions!

• Marked as answer by J I M B O 11 hours 6 minutes ago

Can you share that script in case it will help other readers in a future?

Thanks in advance.