Don’t you wish to know how far along that backup/restore has come? If you’re running it manually, and you remembered to add the STATS parameter, you can see it in the default output, but what if it’s coming from an enterprise tool, like Commvault?
It would also be great to get the progress on other operations, like shrinks, DBCC CheckDB, etc. While not every operation will report its progress, this little script will report progress on whatever operations do provide it.
SELECT r.session_id
,DB_NAME(r.database_id)
,r.status
,r.command
,r.wait_type
,r.percent_complete
,CASE WHEN estimated_completion_time < 36000000 THEN '0'
ELSE ''
END
+ RTRIM(r.estimated_completion_time / 1000 / 3600) + ':'
+ RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 3600 / 60),2)
+ ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 60), 2)
AS TimeRemaining
,GETDATE() + ( CASE WHEN estimated_completion_time < 36000000 THEN '0'
ELSE ''
END
+ RTRIM(r.estimated_completion_time / 1000 / 3600)
+ ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 3600 / 60), 2)
+ ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 60), 2) )
AS EstimatedCompletionTime
FROM sys.dm_exec_requests r
WHERE r.percent_complete > 0