Disaster Recovery, Performance, SQL Server

Reporting DBCC SHRINKFILE Progress in SQL Server

I’ve been doing some large scale data deletion from SQL Server after some of our SharePoint databases got blown out of all proportion due to a simple versioning misconfig.

We’ve been deleting hundreds of gigs out of 2 content databases and this leaves us with the inevitable problem of needing to shrink the MDF files to get the space back. The immense fragmentation problems this will cause us is well documented, but today we have the more immediate problem of trying to tell how much longer the DBCC command is going to take.

Well, we can’t actually tell that precisely. The best we can do is make an educated guess based on how much movement is occuring in the data files via Resource Monitor. There is one other tool you can use in your guestimate in that certain types of command will report their estimated completion via the dm_exec_requests DMV. If you know how long you’ve been running for so far, and how far through processing SQL Server thinks it is, you can guess fairly accurately when you’ll be done

The following query will tell you SQL Servers best guess at how far through processing your DBCC command is:

-- SELECT all active requests across all databases. 
-- NOTE: Percentage complete is only reported for certain command types (e.g. backups, restores, DBCCs etc)
SELECT	
	sys.dm_exec_sql_text.text AS  CommandText,
	sys.dm_exec_requests.Status AS Status,
	sys.dm_exec_requests.Command AS CommandType, 
	db_name(sys.dm_exec_requests.database_id) AS DatabaseName,
	sys.dm_exec_requests.cpu_time AS CPUTime, 
	sys.dm_exec_requests.total_elapsed_time AS ElapsedTime, 
	sys.dm_exec_requests.percent_complete AS PercentageComplete
	
FROM	
	sys.dm_exec_requests 

CROSS APPLY 
	sys.dm_exec_sql_text(sys.dm_exec_requests.sql_handle) 

Note that standard DML commands (INSERT/UPDATE/DELETE etc) do not report progress as far as I’m aware. But there are a number of DBCC, INDEX and BACKUP/RESTORE commands that do and this can be useful during long running maintenance operations.

I’m not sure how accurate the percent_complete column is overall. For example during SHRINKFILE operations it seems to have a strong urge to tell me everything is around 69% complete within a few minutes, then spend another hour grinding. I’ve had more luck with accuracy when using it for backups so your mileage may vary

NOTE: This query can also act as a basic “What’s running right now” query if a server seems to be struggling. This can help identify long running queries or backups/index rebuilds that have overran the maintenance window

I hope that helps someone

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s