Running SQL 2012 R2…
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) – 12.0.5207.0 (X64) Standard Edition (64-bit)
Ran the shrink command
DBCC SHRINKDATABASE(databaseNameHere)
(Yes, I know this shouldn't be done regularly, if ever… we have a 200 GB database and cleared out many years worth of data, and should be able to reclaim about 100 GB of space)
When I checked the status of the task at 1.5 hours, it was 49.1xxx percent_complete.
It's been running for 2.5 hours… and now at 49.5xxx percent_complete.
Additionally, just in the last 20 minutes, the estimated_completion_time (found in sys.dm_exec_requests) has gone from 8,741,035 milliseconds to 9,385,086 milliseconds…
There is still a ton of space available on the drive. It is a development/test database that nobody is using… so whats the deal? why does the estimated time keep increasing?
I've been using sp_who2 active to verify there are no blocks…
Best Answer
SHRINKDATABASEandSHRINKFILEwon't actually release the space to disk until the very last moment: it has to move all the contents around within the files first (which is the part that takes a long time).For why the progress doesn't seem constant: the free/used space is spread out across a large file, so it is going to "skip ahead" when it encounters an empty patch and "slow down" when it hits a section of used pages.
As mentioned in the comments, I would highly recommend using
SHRINKFILEinstead ofSHRINKDATABASE, since you can control the target sizes of each individual file, and give each one a reasonable target. For example, I usually try to leave 15-25% free space in each data file.