Saturday, March 31, 2012

Monitor SQL Server Backup job duration

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.

    1 comment: