A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Estimated query completion time

17 February 2011 Don-Rickman SQL Server - Admin (0)

Problem: How long is it going to take for my restore to finish? Hell, any query I'm concerned with.

sys.dm_exec_requests :

Returns information about each request currently executing within SQL Server

 

Solution

select command
, [text]
, start_time
, percent_complete
, cast(
      cast(
            (estimated_completion_time/1000.00)/60
            as decimal(10,2)
     ) as varchar(16)
) + ' Minutes' [time_remaining]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in
      ('RESTORE DATABASE'
      , 'RESTORE LOG')
-- get a specific database
and [text] like '%<database name>%'