5

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 ?

9

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.

1

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.

sp_whoisactive Output

  • @AA.SC Normally we use sp_whoisactive on all production and local servers but somehow missed that it contains this columns. Thanks – aasim.abdullah Oct 21 '14 at 14:48
  • I agree with Aaron here for many environment they would not allow any outside code to be installed howsoever safe it would be immediately. In that case sys.dm_exec_request would be better solution. Again if its given in DMV why raise a change and move new code on prod agreeing to fact that sp_whoisactive is good. – Shanky Oct 21 '14 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.