Home
> DMO, SQL Server, T-SQL, Uncategorized > Determining Estimated Completion Time from sys.dm_exec_requests
Determining Estimated Completion Time from sys.dm_exec_requests
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:
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/1000)/3600) < 10 THEN '0' + CONVERT(VARCHAR(10),(estimated_completion_time/1000)/3600) ELSE CONVERT(VARCHAR(10),(estimated_completion_time/1000)/3600) END + ':' + CASE WHEN ((estimated_completion_time/1000)%3600/60) < 10 THEN '0' + CONVERT(VARCHAR(10),(estimated_completion_time/1000)%3600/60) ELSE CONVERT(VARCHAR(10),(estimated_completion_time/1000)%3600/60) END + ':' + CASE WHEN ((estimated_completion_time/1000)%60) < 10 THEN '0' + CONVERT(VARCHAR(10),(estimated_completion_time/1000)%60) ELSE CONVERT(VARCHAR(10),(estimated_completion_time/1000)%60) END AS [Time Remaining], percent_complete, * FROM sys.dm_exec_requests WHERE percent_complete > 0
Categories: DMO, SQL Server, T-SQL, Uncategorized
DMO, SQL Server, T-SQL
I think this is slightly cleaner, using fewer CASE expressions and only repeating the column name 4 times. Though I have no idea how well the comment will format it. RTRIM() is my lazy shorthand for conversion but if you’re a stickler about being explicit I think it is still less complex.
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)
Thanks, Aaron. I will put this in my toolbox.