sql
· 1.0 KiB · SQL
Raw
--TEST
SELECT r.session_id AS [Session_Id]
,r.command AS [command]
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
,start_time AS [Start Time]
,GETDATE() AS [Current Time]
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated 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) 'Statement text'
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command like 'RESTORE%'
or command like 'BACKUP%'
| 1 | --TEST |
| 2 | SELECT r.session_id AS [Session_Id] |
| 3 | ,r.command AS [command] |
| 4 | ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete] |
| 5 | ,start_time AS [Start Time] |
| 6 | ,GETDATE() AS [Current Time] |
| 7 | ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time] |
| 8 | ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] |
| 9 | ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min] |
| 10 | ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours] |
| 11 | ,CONVERT(VARCHAR(1000), ( |
| 12 | SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE |
| 13 | WHEN r.statement_end_offset = - 1 |
| 14 | THEN 1000 |
| 15 | ELSE (r.statement_end_offset - r.statement_start_offset) / 2 |
| 16 | END) 'Statement text' |
| 17 | FROM sys.dm_exec_sql_text(sql_handle) |
| 18 | )) |
| 19 | FROM sys.dm_exec_requests r |
| 20 | WHERE command like 'RESTORE%' |
| 21 | or command like 'BACKUP%' |