Friday, February 8, 2013

Estimated time to complete...?

Have you ever been restoring a database and wondered when it might complete?  Perhaps you wanted to give an update to others waiting on that restore?  The following query will not only give you the percent complete and estimated time to complete for backups and restores, but also ROLLBACKs, ALTER INDEX ... REORGANIZE, and many other DBCC commands.  Comes in handy - I'm actually in the middle of a restore right now!


SELECT  percent_complete AS 'PctComplete'
       ,start_time AS 'StartTime'
       ,command AS 'Command'
       ,b.name AS 'DatabaseName'
       ,DATEADD(ms, estimated_completion_time, GETDATE()) AS 'EstimatedEndTime'
       ,( estimated_completion_time / 1000 / 60 ) AS 'EstimatedMinutesToEnd'
FROM    sys.dm_exec_requests a
        INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE   estimated_completion_time > 0