All of my friends say, “How much longer will it run?”

I have frequently needed to see how much longer a backup was going to run, or how much longer a restore was going to run.  This little bit of code is something I use to help me know how to plan.  I replace “Backup” with “Restore” if I want to see how much longer to plan for a restore.  I also use it for when I am tracking a rollback.  I remove the where clause and get a large list, the one that has a percentage and not a clear explanation is usually the one I want.   Sometimes the time remaining is lie.  I have had it imply 4 hours, when it took 13.  The comfort is that I can tell it is still working.


SELECT command, percent_complete,
'elapsed' = total_elapsed_time / 60000.0,
'remaining' = estimated_completion_time / 60000.0
FROM sys.dm_exec_requests
WHERE command like 'BACKUP%'

The song for this post is “Just Another Girl” by The Killers

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

2 thoughts on “All of my friends say, “How much longer will it run?”

  1. Samuel Jones says:

    I use a similar query, but I’ve modified it so it works on anything with an estimate
    SELECT [r].[session_id]
    ,[r].[command]
    ,CONVERT(NUMERIC(6, 2), [r].[percent_complete]) AS [PERCENT Complete]
    ,CONVERT(VARCHAR(20), DATEADD(ms, [r].[estimated_completion_time],
    GETDATE()), 20) AS [ETA COMPLETION TIME]
    ,CONVERT(NUMERIC(6, 2), [r].[total_elapsed_time] / 1000.0 / 60.0) AS [Elapsed MIN]
    ,CONVERT(NUMERIC(6, 2), [r].[estimated_completion_time] / 1000.0 / 60.0) AS [ETA MIN]
    ,CONVERT(NUMERIC(6, 2), [r].[estimated_completion_time] / 1000.0 / 60.0
    / 60.0) AS [ETA Hours]
    ,CONVERT(VARCHAR(100), ( SELECT SUBSTRING([dm_exec_sql_text].[text],
    [r].[statement_start_offset] / 2,
    CASE WHEN [r].[statement_end_offset] = -1
    THEN 1000
    ELSE ( [r].[statement_end_offset]
    – [r].[statement_start_offset] )
    / 2
    END)
    FROM [sys].[dm_exec_sql_text]([r].[sql_handle])
    ))
    FROM [sys].[dm_exec_requests] [r]
    WHERE [r].[estimated_completion_time] > 0

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