SQL Server – How to Detect DBCC ShrinkDatabase Completion Percentage?

sql server

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 ?

Best Answer

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.

Related Question