How to Find Out the Progress of Maintenance Tasks

You kick off a few maintenance tasks on your SQL Server instance, or they they were scheduled to start via a job which may be slowing your server down. For example a full backup, or a DBCC CHECKDB, or maybe even shrinking a file. You might be getting a touch nervous of the potential impact to users. Often in these situations, what you want, is to find out the progress of each task, and how long it has to go until completion.

To solve that problem, I’ll share a nice query using the sys.dm_exec_requests dynamic management view. I have found this query is one of the queries I use the most and has proved quite valuable.

In sys.dm_exec_requests, there are a few very useful columns, namely, percent_complete and estimated_completion_time. According to Microsoft, percent_complete returns a useful value for the commands listed below. Apparently estimated_completion_time is only internal, but in reality, it’s the same as percent_complete, in that it returns a useful value for the commands below:
ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE or LOG
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE or LOG
ROLLBACK
TDE ENCRYPTION

Those commands cover most maintenance operations (I get it, not all of them). If the command is not one of above, then it’ll show 0 in the percent_complete column and the current time in the estimated_completion_time column – not useful at all in those cases.

With these two columns and the addition of of some of the other columns, like sessoin_id, command and total_elapsed_time we can get a very good idea of the progress of our maintenance tasks and how long they have till they finish.

SELECT 
	r.session_id,
	r.command,
	DB_NAME(r.database_id) AS [database_name],
	CONVERT(VARCHAR(10), (r.total_elapsed_time / 1000) / 86400) + ':' + CONVERT(VARCHAR(20), DATEADD(s, (r.total_elapsed_time / 1000), 0), 108) AS [dd:hh:mm:ss elapsed_time],
	CONVERT(DECIMAL(5,2), r.percent_complete) AS percent_complete,
	CONVERT(DATETIME2(0), DATEADD(MILLISECOND, r.estimated_completion_time, GETDATE())) AS estimated_completion_datetime,
	CONVERT(DECIMAL(10,2), r.estimated_completion_time / 1000.0 / 60.0 ) AS estimated_completion_min,
	CONVERT(DECIMAL(10,2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS estimated_completion_hours,
	t.[text] AS sql_text
	--,r.*
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.percent_complete <> 0
ORDER BY r.total_elapsed_time DESC;

The results will look like this:

You can clearly see the basic details session ID, the command it’s running, and the database it’s running on. Moving to the right provides the details you’re after though: the elapsed time in dd:hh:mm:ss format (don’t need milliseconds), the percentage complete, the estimated completion time, the estimated completion time remaining in minutes, and completion time remaining in hours. Then finally the SQL text that the command is running.

In my example above, there is a full backup running, and an integrity check running. Both on the database called MyDatabase. The full backup, session 54, has been running for 36 seconds and is 65% complete. It is due to complete 8:43am 26th October 2020 and has about 20 seconds remaining remaining. The integrity check, session 70, is currently up to the DBCC TABLECHECK phase of the DBCC CHECKDB command which has been running for 32 seconds and is 15% complete. It is due to complete at 8:46am 26th October 2020 and has just under 2.5 minutes remaining. If these were long running tasks, we could use the estimated_completion_hours column too. In case you were wondering, DBCC CHECKDB runs three phases, DBCC CHECKALLOC, DBCC TABLECHECK and DBCC CHECKCATALOG. You can read more about it in Microsoft’s documentation.

You can then also examine the sql_text column, which provides the whole statement that was executed. This can help understand exactly what is running. Perhaps a task is taking longer than normal and you want to check the parameters of the task?

Here, we can easily get a very good picture of what maintenance tasks are currently running, and how long they have left until they complete. If other people are asking you about the progress of the maintenance tasks (perhaps users are now making a fuss about slow performance?), you could even take a screenshot of the results and give it to them rather than explaining things. Much easier. Easier is good.

I have also included the line with “–,r.*”, which could be uncommented to return a bit more extra information, for instance the columns cpu_time, reads, writes and dop can provide more details of how the command is performing.

Hopefully this’ll help you be more prepared when you or other people want to know if maintenance tasks are running and how far they have progressed.