As I am waiting for a backup to complete I decided to do something useful. Here’s a short query that shows running queries and displays estimated finish time:
set nocount on select er.session_id, er.start_time, st.text, er.percent_complete, dateadd(ms, datediff(ms, start_time, getdate()) * 100 / percent_complete, start_time) ETA from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text(sql_handle) st where er.percent_complete > 0
I saw it in once in Jack Vamvas’s blog, but couldn’t find it, so I decided to write my own version.
Right now I am running it in a loop (using WAITFOR DELAY and GO 1000) to pass the time quicker while backup is finishing. One word of notice – it does not apply to all queries, only to those that report progress in percent_complete column of sys.dm_exec_requests – so there’s no way for any of DML queries to show there. I use it for rough progress estimate of backups/restores/DBCC.