1

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.

Paul Randal
  • 7,194
  • 1
  • 36
  • 45
Aaron Alton
  • 1,138
  • 11
  • 10

2 Answers2

2

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

Paul Randal
  • 7,194
  • 1
  • 36
  • 45
1

When you say you can't seem to kill it, are you trying to cancel the job? Or are you killing the SPID from activity monitor?

BradC
  • 2,220
  • 4
  • 27
  • 36
  • Thanks, Brad. I should have been more specific. Had tried that...now my spid is stuck in the KILLED/ROLLBACK state. – Aaron Alton Aug 24 '09 at 18:21
  • Are the CPU and IO on the SPID still advancing? If so, then all you can do is wait until the rollback completes. If not, then the spid might be actually hung, and you'd have to restart the SQL instance to get rid of it. DON'T restart SQL unless you're sure its not still active in rolling back, or the database will stay in "recovering" mode (and inaccessable) until the rollback is complete. – BradC Aug 24 '09 at 18:36
  • CPU > 0; Physical IO > 0. Wait time is increasing, but that's it. – Aaron Alton Aug 24 '09 at 18:49
  • So if CPU and IO aren't still moving, then you probably need to restart SQL. I'd probably want to restart the server entirely after a SAN blip, anyway, but that's just me. – BradC Aug 24 '09 at 19:02