Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run).
For example, I’ve create a database call DbccTest with a single table. In one connection I do:
INSERT INTO t1 VALUES (1, 1);
And in another connection I do:
DBCC CHECKDB (DbccTest) WITH ALL_ERRORMSGS, NO_INFOMSGS;
Look what gets printed in the error log:
2009-05-04 16:03:21.55 spid54 1 transactions rolled back in database ‘DbccTest’ (14). This is an informational message only. No user action is required.
2009-05-04 16:03:21.91 spid54 DBCC CHECKDB (DbccTest) WITH all_errormsgs, no_infomsgs executed by ROADRUNNERPR\paul found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
I’ve highlighted the weird part in bold – it looks like crash recovery ran on the DbccTest database. What’s going on?
Well, crash recovery *DID* run on the DbccTest database – only it ran crash recovery into a hidden database snapshot. In the first part of the very long (well, 13 pages – not as long as the 70 page description in the 2008 internals book) post on how DBCC CHECKDB works (see CHECKDB From Every Angle: Complete description of all CHECKDB stages), I explain why DBCC CHECKDB needs a transactionally-consistent, point-in-time view of the database to run consistency checks on. In 2005 I changed the code to use a database snapshot – which by it’s very nature provides a point-in-time, transactionally-consistent view of the database. When a database snapshot is created on a database, the transaction log of that database is examined, and crash-recovery is run on it, but *into the database snapshot* – the source database is totally unaffected. The message in the error log is from DBCC CHECKDB‘s hidden database snapshot starting up – but it’s pretty misleading, I must admit.
The database ID in parentheses is the database ID of the database snapshot, not the actual database – further confusing things.
One more twist to this behavior is that if there is a very long-running transaction in the database being consistency-checked, the database snapshot creation could take hours (because it has to wait for the long-running transaction to rollback, in the snapshot). If you think that the consistency check has hung, and try to kill it, you won’t be able to and the SPID will appear as if it’s in rollback. This is confusing because DBCC CHECKB doesn’t do anything – so shouldn’t have anything to rollback – but it’s the database snapshot creation that’s the problem. Once crash recovery has started on the database snapshot, it can’t be interrupted – so you have to wait for it to finish (and the database snapshot to be created) before the DBCC command will actually stop, and remove the database snapshot. It’s a weird side-effect, but a necessary one unfortunately. The recovery code could be changed to check for attention signals every so often I suppose, but I’m not holding-my-breath for that change.
Hope this helps.