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.

    5 comments:

    1. Apa arti taruhan 1X2 ? Bagaimana cara hitung rumus main 1X2? Jenis Bet 1X2 Sbobet dalam taruhan bola umumnya juga dikenal dengan sebutan "fix-odds". Bila kita sebelumnya sudah pernah bermain bola taruhan handicap (Baca Selengkapnya Disini...)

      ReplyDelete
    2. Good Blog, well descrided, Thanks for sharing this information.
      Big Data and Hadoop Online Training

      ReplyDelete