How to check the progress of the Shrink Database task in SQL Server

When compacting large databases in SQL Server, whether or not its done via the DBCC SHRINKDATABASE command or done via the SQL Server Management Studio, you will experience a very long delay in the command returning.

To check how the process is going, you cannot simply look at a progress bar or se a percentage some nice place.

But you can use the system views in SQL Server to see how its going.

Simply open up a new query window in your SQL Server Management Studio and issue the following SQL:

SELECT 
	percent_complete, 
	start_time, 
	status, 
	command, 
	estimated_completion_time, 
	cpu_time, 
	total_elapsed_time
FROM 
	sys.dm_exec_requests
WHERE
	command = 'DbccFilesCompact'

That will give you a nice little table where you can see your compact task and how far it has gone in its progress.

But prepare to be disapointed - it is very slow to compact databases.

 

Comments (3) -

Jon Baddeley 1/21/2013 6:15:28 PM

Thanks, I've just found this really useful as Management Studio gives no feedback on progress.

Jagdeep Mankotia 3/26/2013 6:14:40 AM

Thank you, its working fine and not encountered any issue yet.
Use * for more columns in it.

Awesome, this worked very well.  Thanks!

Comments are closed