Solution:
T-SQL:
SELECT
r.session_id as 'SessionID'
,r.command as 'Command'
,[Status] as 'Status'
,DB_Name(database_id) as 'Database'
,CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) as 'Total Elapsed Time (Min)'
,CONVERT(NUMERIC(6,2),r.percent_complete) as '% Complete'
,start_time as 'Start Time'
,CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) as 'Estimated Completion Time'
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) as 'Estimated Completion Time (Min)'
,last_wait_type as 'Last Wait Type'
,wait_time as 'Wait Time'
,wait_resource as'Wait Resource'
,CONVERT(VARCHAR(1500),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1500
ELSE (r.statement_end_offset - r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) as 'Statement'
FROM sys.dm_exec_requests r
WHERE command in
('RESTORE DATABASE','BACKUP DATABASE')
--Target Database Name parsed
SELECT
SUBSTRING(
CONVERT(VARCHAR(1500),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1500
ELSE (r.statement_end_offset - r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))),
CHARINDEX('[',
CONVERT(VARCHAR(1500),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1500
ELSE (r.statement_end_offset - r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle)))),
CHARINDEX(']',
CONVERT(VARCHAR(1500),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1500
ELSE (r.statement_end_offset - r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))))
- CHARINDEX('[',
CONVERT(VARCHAR(1500),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1500
ELSE (r.statement_end_offset - r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle)))) + 1) as 'Target Database'
,
r.session_id as 'SessionID'
,r.command as 'Command'
,[Status] as 'Status'
,DB_Name(database_id) as 'Database'
,CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) as 'Total Elapsed Time (Min)'
,CONVERT(NUMERIC(6,2),r.percent_complete) as '% Complete'
,start_time as 'Start Time'
,CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) as 'Estimated Completion Time'
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) as 'Estimated Completion Time (Min)'
,last_wait_type as 'Last Wait Type'
,wait_time as 'Wait Time'
,wait_resource as'Wait Resource'
,CONVERT(VARCHAR(1500),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1500
ELSE (r.statement_end_offset - r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) as 'Statement'
FROM sys.dm_exec_requests r
WHERE command in
('RESTORE DATABASE','BACKUP DATABASE')
Image #1:
Select
db_name(vfs.database_id) as 'DB',
mf.name as 'Logical File Name',
mf.physical_name as 'Physical File Name',
pr.io_type as 'IO Type'
,sum(pr.io_pending_ms_ticks) as sum_io_pending_ms_ticks
,count(*) as 'Pending IOs'
From
sys.dm_io_pending_io_requests pr
Left Join sys.dm_io_virtual_file_stats(null, null) vfs on vfs.file_handle = pr.io_handle
Left Join sys.master_files mf on mf.database_id = vfs.database_id and mf.file_id = vfs.file_id
where
pr.io_pending = 1
and
db_name(vfs.database_id) is not null
Group By
db_name(vfs.database_id),
mf.name,
mf.physical_name,
pr.io_type
Select distinct os.scheduler_id, os.cpu_id, ipir.io_type, ipir.scheduler_address, ipir.io_handle, os.pending_disk_io_count
From sys.dm_io_pending_io_requests ipir
Inner Join sys.dm_os_schedulers os ON ipir.scheduler_address = os.scheduler_address
Select * from sys.dm_io_pending_io_requests
|
|
|
|
|
|