The controllers on one of my cluster's SAN's are periodically restarting themselves. Apparently it can be fixed by a firmware update, but until we can apply the update in a few days, I have to live with the issue. The problem is, if one of them fails while DBCC CHECKDB is running (during nightly maintenance), CHECKDB hangs. I can't seem to kill it….it shows that it's waiting on ASYNC_IO_COMPLETION forever. Do I need to cycle the instance, or is there another way to kill CHECKDB?
SQL Server 2005 RTM & SP2 instances.
Best Answer
Ok - this is proper behavior. What's happening is that you've killed DBCC CHECKDB while it's still creating the database snapshot it needs. Part of creating the snapshot is running crash recovery on the database being checked, but into the context of the database snapshot. Crash recovery cannot be stopped, and a spid that's running it cannot be killed. So - your only options are to wait for it to finish, or cycle the instance - which will drop the snapshot. When I changed CHECKDB to use database snapshots we knew this would be a potential issue, but there was no alternative.
Check out this blog post where I explain a bit more: Do transactions rollback when DBCC CHECKDB runs?
Cheers