http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/03/31/monitor-sql-server-backup-job-duration/ Printed 2014/09/02 03:29AM
Monitor SQL Server Backup job duration
2012/03/31if 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:
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
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.