Archive

Archive for November 21, 2012

Determining Estimated Completion Time from sys.dm_exec_requests

November 21, 2012 2 comments

According to Books Online, the following list of commands will generate a percent complete and an estimated completion time in the sys.dm_exec_requests DMO:

Percent Complete Commands

You can use this to determine approximately how long a backup, restore or DBCC command will run. This query will get you a nicely formatted time value.

USE master
GO

SELECT
CASE
WHEN estimated_completion_time < 36000000
THEN '0'
ELSE ''
END
+ RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],
percent_complete,
*
FROM sys.dm_exec_requests
WHERE percent_complete > 0