I was waiting to implement a change the other day and found the DBCC job was still running. As this was a production system I was not too keen on cancelling the DBCC check. I thought about restarting it after I had completed my change but although the DBCC had been running for nearly an hour I was not sure how long it would take to complete, so it was then that I realised that the DBCC command was one of those commands that populates the
percent_complete column in the
sys.dm_exec_requests DMV, so I modified my backup/restore progress query to give me an ETA for when the DBCC would complete:
SELECT r.session_id, CONVERT(NVARCHAR(22),DB_NAME(r.database_id)) AS [database], r.command AS [dbcc command], s.last_request_start_time AS [started], DATEADD(mi,r.estimated_completion_time/60000,getdate()) AS [finishing], DATEDIFF(mi, s.last_request_start_time, (dateadd(mi,r.estimated_completion_time/60000,getdate()))) - r.wait_time/60000 AS [mins left], DATEDIFF(mi, s.last_request_start_time, (dateadd(mi,r.estimated_completion_time/60000,getdate()))) AS [total wait mins (est)], CONVERT(VARCHAR(5),CAST((r.percent_complete) AS DECIMAL (4,1))) AS [% complete], GETDATE() AS [current time] FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.[session_id]=s.[session_id] WHERE upper(r.[command]) like 'DBCC %'
A sample ouput from a test database called er…test is shown below: