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 < 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
Categories: DMO, SQL Server, T-SQL, Uncategorized
DMO, SQL Server, T-SQL
Leave a Reply
PASS Summit 2018
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.