SQL Server shrink command 'estimated_completion_time' keeps going up

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...

SHRINKDATABASE and SHRINKFILE won'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 SHRINKFILE instead of SHRINKDATABASE, 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.

StackExchange Database Administrators Q#203028, answer score: 4