Do transactions rollback when DBCC CHECKDB runs?

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:

BEGIN TRAN
INSERT INTO t1 VALUES (1, 1);
GO

And in another connection I do:

DBCC CHECKDB (DbccTest) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
 

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.

7 thoughts on “Do transactions rollback when DBCC CHECKDB runs?

  1. The number in parenthesis appears to be the DB_ID() of the database that the transaction rolled back in:

    1 transactions rolled back in database ‘DbccTest’ (14).

    It will not be the same as the db_id() of the “real” database.

    At least, that is how I made myself sleep at night after I saw the message :)

  2. Paul,
    I experienced this issue as well. Obviously loosing data is a bad thing and keeping a DB system from being inconsistent is a good thing. What other things would you recommend to avoid loosing data as part of DBCC Checkdb (I’m thinking — issue a checkpoint before the DBCC check). Can you think of any other commands to run before the DBCC integrity check (for scheduled maintenance)?

    thanks

    1. I’m confused – the blog post doesn’t say anything about DBCC CHECKDB losing data. Issuing a checkpoint isn’t going to help with the snapshot creation time, as checkpoints have nothing to do with transactions.

  3. Whew :)
    I stumbled across this post looking for a separate issue and it really calmed my nerves. I had no idea what the “recovery” looking entries were caused by and no idea how to begin troubleshooting them.

    Thanks,

  4. I have seen a message which is a little different than you mentioned above. It says…1 transactions rolled back in database ‘msdb’ (35:0). This is an informational message only. No user action is required. Why 35:0 is mentioned in place of db id?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.