[SCRIPT] – How to monitor Backup, Restore, DBCC etc… WITH ETA

Hi everyone,

Sometimes we need to monitor a specific activity, for example a Backup, Restore, DBCC etc… And we also need to have, possibly, an estimated time of “arrival” (or ETA) it takes to finish this activity, right? HERE YOU ARE! So I decided to write this script a long time ago, and it’s one of those that are part of my everyday “Swiss Army Knife”.

I hope this script can be as useful to you as it is to me!

--Bruno Bragatto - Senior DBA
-- Monitoring Backup, Restore, DBCC etc...

SELECT r.session_id,s.login_name,DB_NAME(r.database_id) as database_name, r.command,CONVERT(NUMERIC(20,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(20,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(20,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(20,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
s.status,
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset) END)
FROM sys.dm_exec_sql_text(sql_handle))) as 'Script'
FROM sys.dm_exec_requests r left join sys.dm_exec_sessions s on (s.session_id = r.session_id) 
WHERE command LIKE 'BACKUP%' OR command LIKE 'RESTORE%' OR command LIKE 'DBCC%' OR command LIKE '%INDEX%' OR command LIKE '%STATISTICS%' OR command LIKE '%ALTER%'