dbascript.com

--SCRIPT 1
SELECT  session_id AS SPID ,
        command ,
        a.text AS Query ,
        start_time ,
        percent_complete ,
        DATEADD(second, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
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' )
--run at 11:04
--percent complete: 63.56613
--estimated completion time: 2012-04-03 12:38:55.033
 
--SCRIPT 3
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' )
 
--SCRIPT 3
SELECT  r.session_id ,
        r.command ,
        CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete] ,
        CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,
                                     GETDATE()), 20) AS [ETA Completion Time] ,
        CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,
        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,
        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0
        / 60.0) AS [ETA Hours] ,
        CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,
                                                  r.statement_start_offset / 2,
                                                  CASE WHEN r.statement_end_offset = -1
                                                       THEN 1000
                                                       ELSE ( r.statement_end_offset
                                                              - r.statement_start_offset )
                                                            / 2
                                                  END)
                                 FROM   sys.dm_exec_sql_text(sql_handle)
                               ))
FROM    sys.dm_exec_requests r
WHERE   command IN ( 'RESTORE DATABASE', 'BACKUP DATABASE' )
 
--SCRIPT 4
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' )
 
--SCRIPT 5
SELECT  dmr.session_id ,
        dmr.status ,
        dmr.start_time ,
        dmr.command ,
        dmt.TEXT ,
        dmr.percent_complete
FROM    sys.dm_exec_requests dmr
        CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt
WHERE   dmr.command IN ( 'ALTER', 'Backup', 'Restore', 'DBCC', 'Rollback',
                         'TDE' )
 
--SCRIPT 6
SELECT  percent_complete ,
        ( estimated_completion_time / 1000 ) / 60 Estimated_completion_time_Mins ,
        ( total_elapsed_time / 1000 ) / 60 Total_Elapsed_Time_Mins ,
        DB_NAME(Database_id) DBName ,
        *
FROM    sys.dm_exec_requests
WHERE   session_id = 129
 
--SCRIPT 7
USE master
SELECT  session_id AS [SPID] ,
        CONVERT(VARCHAR(50), start_time, 120) AS [StartTime] ,
        percent_complete AS [PercentageComplete] ,
        CONVERT(VARCHAR(50), DATEADD(second, estimated_completion_time / 1000,
                                     GETDATE()), 120) AS [EstimatedCompletionTime] ,
        command AS [Command] ,
        a.text AS [OriginalQuery]
FROM    sys.dm_exec_requests r
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE   r.command LIKE 'BACKUP%'
        OR r.command LIKE 'RESTORE%'