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
Andrea Allred presents RoyalSQL 
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
Nice! I like it! Thanks for sharing!