Today's script allows you to keep track of how long left a statement has before it completes. I find this useful for example when restoring databases. All you need to do is simply change the der.command = as required.
/*
-----------------------------------------------------------------
Estimated Time to Complete
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
SELECT
DB_NAME(database_id) AS DatabaseName,
der.session_id,
der.command,
der.[status],
p.lastwaittype,
p.waitresource,
der.percent_complete,
der.estimated_completion_time,
CONVERT(VARCHAR(10),
DATEADD(MS, der.estimated_completion_time, 0),8) AS EstimatedTimeLeft
FROM
sys.dm_exec_requests AS der WITH(NOLOCK)
INNER JOIN sys.sysprocesses AS p WITH(NOLOCK) ON p.spid = der.session_ID
WHERE
der.command = 'RESTORE DATABASE'
Enjoy!
Chris
