Shrinking a database is not fast. In fact, it tends to be incredibly slow, and there's nothing you can do to speed it up, other than to have less data to shrink.
How does shrinking work?
When you shrink a data file, SQL Server takes the 8k pages from the end of the file, and moves them to a (random) spot at the beginning of the file. It moves the pages one page at a time. In a way, the page-by-page movement of data is similar to the way a REORGANIZE
works, except in reverse. Because the pages are moved individually, to a (random) new spot, it will "disorganize" pages by making previously-contiguous pages non-contiguous, creating fragmentation.
The page-by-page movement is inherently slow, which is why it is faster to REBUILD
highly-fragmented indexes than to REORGANIZE
them.
REBUILD
instead
Normally, I'd suggest that you just REBUILD
into a new filegroup, then remove or shrink the old (empty) filegroup. The REBUILD
into the new filegroup will be faster than shrinking, because REBUILD
if faster then REORGANIZE
(and "backwards REORGANIZE
" aka shrink). Then, with the old filegroup empty, it will be quick to remove or shrink when it is empty or almost empty, because there are no/few pages to remove.
And because shrinking creates fragmentation, you are going to need to do a REORGANIZE
after you shrink anyway.
LOBing a curveball
In your scenario, LOB data represents 2458GB out of 3108GB total data (80%). When you do a REBUILD
it will move the B-Tree pages, but it will not move the LOB data. So if you rebuild into a new filegroup, your old filegroup will still be 60+% full.
For the table(s) that have a significant amount of LOB data, you would need to take a different approach. You could create a new table in the new filegroup, migrate data to that new table, then swap table names and drop the old table. You could think of this process as a "manual rebuild"--except doing it this way would allow you to copy LOB data to the new filegroup. I describe this method in a blog post on changing data types, but the exact same method would work in this case--except you would be changing filegroups, rather than changing data types.
Shrinking (nearly) empty filegroups is fast
Shrinking is slow because it has to move allocated pages in order to create contiguous free space at the end of the data file. If the filegroup is empty, the end of the file can be truncated without moving pages and will be very fast.
If the filegroup you are shrinking is PRIMARY
then you won't be able to get it completely empty--PRIMARY
contains all the system tables & metadata. By moving all your user tables out of PRIMARY
, the filegroup will be almost empty, and there will be a very small number of pages to move. Having just a few pages to move will also be pretty fast, particularly compared to moving 2TB of pages.