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:

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
  1. November 21, 2012 at 13:32

    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)

  2. November 21, 2012 at 20:53

    Thanks, Aaron. I will put this in my toolbox.

  1. No trackbacks yet.

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

%d bloggers like this: