How to detect DBCC ShrinkDatabase completion percentage?

Question

Recently we have archived some of data from one of very large production database and need to shrink all data files to reacquire disk space.

Problem is that its taking too much time and we are unable to find out how much work is done by DBCC ShrinkDatabase so we can estimate remaining execution time.

Is there any quick way to get completion percentage of DBCC ShrinkDatabase task ?

Answer 1

Well, you shouldn't ever be using DBCC SHRINKDATABASE, IMHO - if you need to shrink files at all, you should think twice, maybe even three times, and even in the odd case where it really is warranted (hint: this should be rare), you should target each file individually using DBCC SHRINKFILE. Please read every single word on this page.

Anyway, you can see the progress using:

SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE session_id = <spid running the shrink>;

Documentation for sys.dm_exec_requests.

Answer 2

No need to reinvent the wheel, Adam Machanic has written a usefull stored procedure to replace SP_Who and SP_Who2. You can find it here for free.

(http://whoisactive.com/)

This stored procedure has a column “Percent_Complete” which gives you how much % shrinking of database is been completed by Server.