They had scheduled weekly DBCC CHECKDB job running across our user databases. Due to some unknown reasons, the checkdb was taking longer than expected time. Since it was close to the start of their business hours, they had another job to stop this job. They called it as “terminator” job. As per job history, this terminator job ran successfully BUT the checkdb SPID was still there (in sys.sysprocesses). We joined a GoToMeeting session and here are the few things which we tried.
- Kill SPID gave the following message:
SPID xxxx: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
- Found that the SPID which was running checkdb was having a wait of PREEMPTIVE_OS_DEVICEIOCONTROL.
- Wait time kept on increasing in sys.dm_exec_requests.
- No change in CPU and IO columns in sys.dm_exec_requests.
- We ran DBCC OPENTRAN
Found some clues about DTC so we restarted MSDTC but it didn’t help.
Based on wait type it looks like is something that is controlling the IO which is preventing the CHECKDB process from completing. “PREEMPTIVE_OS” means it is actually outside of SQL at that point as SQL is letting the OS perform the operation.
I recalled my earlier blogs about KILLED/ROLLBACK. You can read them using below links.
In short, there really isn’t anything to be done from the SQL perspective, unfortunately, other than a service or server restart. So, in this wait (PREEMPTIVE_OS_DEVICEIOCONTROL), we did restart of SQL Server to get rid of the SPID. This is not very common to see PREEMPTIVE_OS_DEVICEIOCONTROL wait type but if you do, you would search on the internet and might hit this blog and you know what to do now.
Reference: Pinal Dave (https://blog.SQLAuthority.com)