As you might already know that I am a SQL Server Performance Tuning Expert who also acts as a “on-demand consultant” for many organizations across the world who have performance tuning or any other issue related to SQL Server. Once I was working with one of my clients and their DBA asked a question. In this blog, we would learn about possible ways to handle a situation where DBCC CHECKDB is indefinitely waiting for PREEMPTIVE_OS_DEVICEIOCONTROL.
THE SITUATION
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
SQL SERVER – Identify Oldest Active Transaction with DBCC OPENTRAN
Found some clues about DTC so we restarted MSDTC but it didn’t help.
THE ANSWER
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.
SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?
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)
