We have an 8 TB SQL Server 2014 SP1 database, with one primary filegroup, spread across multiple files on different drives. This is part of an AlwaysOn AG with multiple secondaries (some async).
One old table in this db is 6TB, and we truncated it. Now to reclaim this disk space, we can either DBCC ShrinkDatabase
or ShrinkFile
.
We don't care about the usual problems with shrinking like fragmentation, or database growing again etc. because we are only focusing on regaining disk space now.
But these shrink operations are REALLY slow, and are looking for a faster way to do this.
First, are these slow because the changes have to sync to the synchronized-secondary?
Second, is there a way to make shrinks faster?
We thought about couple other options:
Creating another database and moving all (except the old) tables to it...but might need to setup AG all over.
Creating another filegroup and moving all (except the old) table to it...but the old table is on Primary filegroup, which cannot be dropped, right ?
Which method have you guys used in the industry to 'shrink' databases after dropping or truncating large tables?
I have read How do shrink *right* when required? but no answer satisfies my questions.. is there an option other than shrink? Can I move tables off the primary filegroup and delete it?