We had a large data purge recently where a large part of the data in a file was deleted.
File is at 140 GB size but only 40 GB is used space after the purge.
I am trying to shrink the file in small - 1GB - increments like this, so I can better monitor the progress of the shrinks:
dbcc shrinkfile (dbfile1, 148000);
dbcc shrinkfile (dbfile1, 147000);
dbcc shrinkfile (dbfile1, 146000);
Each of the steps above has taken about 30 seconds.
However, when I get to this step, it does not complete even after 20 minutes and I end up aborting it:
dbcc shrinkfile (dbfile1, 137000);
Checking the percent_complete gives me 99% but it is also stuck at that number and does not progress any further:
where session_id = 128
I have checked and defrag'ed the database following the successful shrinks.
Has anyone else faced this issue?
What could be the cause?
__________________________________________________________________________________SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based TablesPersisting SQL Server Index-Usage Statistics with MERGETurbocharge Your Database Maintenance With Service Broker: Part 2