Extended Events for Tracking Backup and Restore Progress

From SQL Server Wiki
Jump to navigation Jump to search

T-SQL Script for Tracking Backup and Restore Progress[edit]

The following code is an alternative to using Extended Events for tracking Backup and Restore Progress and provides estimated time of completion, as well. The code can also be used to track certain other potentially long duration processes including DBCC CheckDB, UpdateStatistics, and SPID KILLs.

 SELECT  DBName             = DB_NAME(r.database_id)
        ,Command            = r.command
        ,SessionStartDT     = CONVERT(CHAR(16),r.start_time,120)
        ,CurrentDT          = CONVERT(CHAR(16),GETDATE(),120)
        ,EstCmdEndDT        = CONVERT(CHAR(16),DATEADD(ss,estimated_completion_time/1000, GETDATE()),120)
        ,CmdTimeToGo        = CONVERT(VARCHAR(10),DATEDIFF(hh,0,DATEADD(ss,estimated_completion_time/1000, 0)))
                            + RIGHT(CONVERT(CHAR(8),DATEADD(ss,estimated_completion_time/1000, 0),108),6)
        ,CmdPctComplete     = r.percent_complete
        ,SessionDuration    = CONVERT(VARCHAR(10),DATEDIFF(hh,0,GETDATE()-r.start_time))
                            + RIGHT(CONVERT(CHAR(8),GETDATE()-r.start_time,108),6)
        ,UserName           = USER_NAME(r.[user_id])
        ,SPID               = r.session_id
        ,BlockingSPID       = r.blocking_session_id 
        ,Query              = (SELECT '--' + CHAR(10) + a.[text] + CHAR(10) AS [processing-instruction(QueryCode)] FOR XML PATH(''),TYPE)
   FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) a 
  WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE','DBCC CHECKDB','KILL','UPDATE STATISTICS')
  ORDER BY DBName
;

"Code provided by Jeff Moden (MS SQL Server MVP) via SQLServerCentral.com"