The Curious Case of… transactions rolling back during DBCC CHECKDB

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

 

Continuing the database snapshot theme from the previous Curious Case post, I had another question from someone who was concerned about transactions rolling back during DBCC CHECKDB. They’d just noticed the messages in the error log saying that when DBCC CHECKDB was executed, it was causing transactions to roll back in the database – and how could that possibly be allowed to happen? They said they panicked and stopped all DBCC CHECKDB executions.

 

There’s no need to panic. The problem is actually a bug in the database snapshot code that’s been there since SQL Server 2005, where it reports the wrong database name.

Let’s try this on SQL Server 2019. First I’ll create a simple database, cycle the error log, and start a transaction:

USE [master];
GO

IF DATABASEPROPERTYEX (N'Company_Snapshot', N'Version') > 0
BEGIN
	DROP DATABASE [Company_Snapshot];
END
GO
IF DATABASEPROPERTYEX (N'Company', N'Version') > 0
BEGIN
	ALTER DATABASE [Company] SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [Company];
END
GO

-- Create a database
CREATE DATABASE [Company];
GO

USE [Company];
GO

CREATE TABLE [t1] ([c1] int);
GO

EXEC sp_cycle_errorlog;
GO

BEGIN TRAN
INSERT INTO [t1] VALUES (1);
GO

Now in a separate window, I’ll create a database snapshot an examine the error log:

CREATE DATABASE [Company_Snapshot]
ON (
	NAME = N'Company',
	FILENAME = N'C:\SQLskills\CompanyData.mdfss')
AS SNAPSHOT OF [Company];
GO
EXEC xp_readerrorlog;
GO

And the pertinent lines from the error log are:

2020-01-15 13:29:30.740 spid58       1 transactions rolled back in database 'Company_Snapshot' (17:0). This is an informational message only. No user action is required.
2020-01-15 13:29:30.740 spid58       Recovery is writing a checkpoint in database 'Company_Snapshot' (17). This is an informational message only. No user action is required.

This is correct; the transaction is being rolled back in the context of the database snapshot, to make it transactionally consistent.

Now let’s try a DBCC CHECKDB:

DBCC CHECKDB (N'Company');
GO
EXEC xp_readerrorlog;
GO

And the pertinent lines from the error log are:

2020-01-15 13:31:52.710 spid58       1 transactions rolled back in database 'Company' (21:0). This is an informational message only. No user action is required.
2020-01-15 13:31:52.900 spid58       DBCC CHECKDB (Company) executed by CRINAN\Paul found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000024:00000087:0001 and first LSN = 00000024:0000003c:0001.

And this is incorrect, and the cause of the concern.

The ‘bug’ is that the name for the internal database snapshot is chosen (by the Database Manager) to be the same as for the real database, and DBCC CHECKDB has no control over that, so it looks like the rollbacks are happening in the real database. But if you look at the database ID in the message, you’ll see that it’s not the same as for the real Company database (which is 5 in this case). So, if you’re ever concerned by a message like this, just look up the database ID of the database you’re running DBCC CHECKDB against and you’ll see that things are fine.

2 thoughts on “The Curious Case of… transactions rolling back during DBCC CHECKDB

  1. Respected Sir,

    Please we are also curious to know 1 more thing about DBCC Checkdb-

    When we execute checkdb, it shows @blobreader in sys.dm_exec_request but it doesn’t show what table it is executing over. Can we find out this – Object-id

    Thank you

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.