The Unkillable DBCC CHECKDB

Q: In the past I’ve noticed some very strange behavior when running ad-hoc consistency checks. If I interrupt DBCC CHECKDB, it sometimes hangs and the SPID says it’s rolling back, but DBCC CHECKDB doesn’t change anything. What’s going on?

A: This behavior is a side effect of how DBCC CHECKDB (and its derivative consistency-checking commands) obtains a transactionally consistent, static view of the database being checked. When I rewrote DBCC CHECKDB for SQL Server 2005, I changed the mechanism to use a database snapshot (which by its very nature gives DBCC CHECKDB what it needs). This is much simpler than the method for SQL Server 2000 (but arguably not as much fun to write and debug), which involved duplicating crash recovery functionality inside DBCC CHECKDB. When a database snapshot is created on a database (which I’ll call the source database), any uncommitted transactions in the source database have to be rolled back inside the database snapshot. The actual transactions themselves in the source database are unaffected, but in the database snapshot it’s as if the transactions rolled back. This gives the transactionally consistent view of the source database.

If there’s a long-running transaction in the source database, it can take a long time to roll back in the database snapshot. If the database snapshot creation is cancelled, the rollback process continues—regardless of whether the database snapshot is being created automatically by DBCC CHECKDB or is user created—because it isn’t possible to interrupt a rollback because it would result in an inconsistent (and potentially corrupt) database.
You could argue that there should be some special-case code in the rollback/recovery part of the SQL Server Storage Engine to cope with the case in which rollback is occurring because of database snapshot creation, and allow the rollback to be interrupted, but that code doesn’t exist.

This behavior also accounts for the error log messages that scare people sometimes. For instance, I just ran DBCC CHECKDB against an example database on my laptop and saw the message show in Figure 1 in the error log. Unless you know what’s going on, this message looks very disturbing—like transactions are being rolled back in the actual user database. But they’re not, it’s just that the informational message doesn’t mention why the transactions are being rolled back.

When you interrupt DBCC CHECKDB and see this behavior, it’s not DBCC that’s refusing to be interrupted, it’s the rollback process as part of the database snapshot creation process. You have no choice but to let it complete or reboot SQL Server (which automatically removes any DBCC CHECKDB–created database snapshots that existed at the time SQL Server shut down).