Monitor SQL Server Backup job duration

2012/03/31

if you run BACKUP DATABASE T-SQL, there is a parameter STATS which reports the percentage complete, However when you run the SQL Agant backup job which is created by maintenance plan, it will be difficult to monitor the percentage of backup process.

here you can use the query below:
==========================================================
SELECT Command,
s.Text,
Start_Time,
Percent_Complete,
CAST(((DATEDIFF(s,start_time,GETDATE()))/3600) as VARCHAR) + ' hour(s), ' +
CAST((DATEDIFF(s,start_time,GETDATE ())%3600)/60 as VARCHAR) + 'min, ' +
CAST((DATEDIFF(s,start_time,GETDATE ())%60) as VARCHAR) + ' sec' as Running_Time,
CAST((estimated_completion_time/3600000) as VARCHAR) + ' hour(s), ' +
CAST((estimated_completion_time %3600000)/60000 as VARCHAR) + 'min, ' +
CAST((estimated_completion_time %60000)/1000 as VARCHAR) + ' sec' as Est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as Est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command IN
('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
==========================================================

in fact this query can estimate other command duration, you can replace the IN condition with other value, here is from BOL

ALTER INDEX REORGANIZE

AUTO_SHRINK option with ALTER DATABASE

BACKUP DATABASE

CREATE INDEX

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKTABLE

DBCC INDEXDEFRAG

DBCC SHRINKDATABASE

DBCC SHRINKFILE

KILL (Transact-SQL)

RESTORE DATABASE,

UPDATE STATISTICS.

the query get the estimate duration based on the data page it read, especially for long duration command, it is more accurate. 2 notes:
1. the estimate is based on the data page which will be went through, sometime it is not accurate, especially for small database.

2. it can not be used for "select" and DML query.