Misleading Message Regarding “Transaction Rollback” While Running DBCC CHECKDB...

We all know the importance of DBCC CHECKDB in MS Sql server. The command “DBCC CHECKDB(database name)” checks the logical and physical integrity of all the objects in the specified database. If corruption has occurred for any reason, the DBCC CHECKDB command will find it, and will display exactly where the problem is. 

I got very interesting thing while running DBCC CHECKDB command in error log file and it was "transactions rolled back in database".

Lets create the same scenario using below script:

USE master
GO
CREATE DATABASE DTEST
GO
USE DTEST
GO
CREATE TABLE T(ID INT,NAME VARCHAR(12))
GO

--NOW WE WILL WRITE INSERT QUERY WITH MISSING COMMIT/ROLLBACK TRAN

BEGIN TRAN
INSERT INTO T VALUES(1,'MICROSOFT')
GO

Now open second query window and execute the below:

USE DTEST
GO
DBCC CHECKDB(DTEST)WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO 

Now open error log file and you will see below message:

2014-08-12 12:39:02.99 spid59      1 transactions rolled back in database 'DTEST' (25). 
This is an informational message only. No user action is required.

If you know little bit about how DBCC CHECKDB works internally it will be easier to understand why this message appears. From SQL Server 2005, DBCC CHECKDB uses a database snapshot to check the objects of 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 from DBCC CHECKDB is for hidden database snapshot not for actual database.

To verify the same we can run DBCC OPENTRAN(). It will list out the open transactions. In our case we will get the message as below:

Transaction information for database 'DTEST'.

Oldest active transaction:
    SPID (server process ID): 56
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (23:69:1)
    Start time    : Aug 12 2014 12:38:16:340PM
    SID           : 0x01

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thanks For Reading This Blog!!!