Day 22 of 31 Days of Disaster Recovery: Which DBCC CHECK Commands Update Last Known Good DBCC

The end of the day is quickly approaching as I finish this blog post. This is day 22 in my series 31 Days of Disaster Recovery, and I want to examine which DBCC CHECK commands update the last known good DBCC check that is tracked in the header of the database. To check this value, I could either dump the header page using DBCC PAGE() or I could just output he header info using DBCC DBINFO(). Both of these functions are officially undocumented, but you can find them documented unofficially all over the web. They are known to be safe commands to use; however, I still recommend that you don’t use them to muck around in production.

So the question is which DBCC CHECK commands update the value. Well, let’s find out. Trial and error is our tool of choice here.

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts
14. Fixing a Corrupt Tempdb
15. Running DBCC CheckTable in Parallel Jobs
16. Disaster Recovery Gems From Around The Net
17. When are Checksums Written to a Page
18. How to CHECKDB Like a Boss
19. How Much Log Can a Backup Log
20. The Case of the Backups That Wouldn’t Restore
21. Who Deleted That Data?

The Test

I executed all of the DBCC CHECK commands, except DBCC CHECKIDENT, of course, because that command isn’t used for consistency checks. After each execution, I would check the value of dbi_dbccLastKnownGood in the database header. If the value had been updated, I dropped and recreated the database before the next test execution.

Normally, you need to enable trace flag 3604 to see the output of commands like DBCC PAGE() and DBCC DBINFO() in the query window message pane. This trace flag redirects output from the SQL log to the console. however, in this case, I’m using the WITH TABLERESULTS option. This option is documented for some DBCC commands, but it works with almost any DBCC command. If I use this option, the output automatically goes to the query window results pane, so there’s no need to use trace flag 3604. This option also makes it easy to insert into a table to query.

For the testing, I created a new database named TestDBCC, added a filegroup and file to it and then created a table with some data in it.

Use master;

-- Drop database if it exists
If DB_ID('TestDBCC') Is Not Null
    Drop Database TestDBCC;
Go

-- Create new database
Create Database TestDBCC;
Go

-- Add a filegroup for testing DBCC CHECKFILEGROUP
Alter Database TestDBCC Add Filegroup TestFG;
Go

-- Add a file to the filegroup
Alter Database TestDBCC Add File (
    Name = N'TestFile',
    FileName = N'C:\bak\TestFile.ndf')
To Filegroup TestFG;
Go

-- Switch to the database
Use TestDBCC;
Go

-- Create a table for testing DBCC CHECKTABLE
Create Table dbo.AllDBs(
    DBID int not null,
    DBName sysname not null,
    -- Create a check constraint for testing DBCC CHECKCONSTRAINTS
    Constraint ckPK Check(DBID > 0))
On TestFG;
Go

-- Add some data to the table
Select database_id, name
From sys.databases;
Go

This is the query I used repeatedly to check the last know good value of the database I created for the test:

-- Table for DBCC results
Declare @DBInfo Table (
    ParentObject varchar(255),
    Object varchar(255),
    Field varchar(255),
    Value varchar(255))

-- Insert DBCC DBINFO into table
Insert Into @DBInfo
Exec sp_executesql N'DBCC DBInfo(''TestDBCC'') With TableResults;';

-- Query for last known good DBCC
Select Value As dbccLastKnownGood
From @DBInfo
Where Field = 'dbi_dbccLastKnownGood';

The results of the testing was:

DBCC Command	Update Last Known Good DBCC?
DBCC CHECKTABLE	No
DBCC CHECKCONSTRAINTS	No
DBCC CHECKFILEGROUP	Yes
DBCC CHECKALLOC	No
DBCC CHECKCATALOG	No
DBCC CHECKDB	Yes

I also tested several different options to see if any particular option affected whether the last good DBCC got updated. None of the optional settings made a difference as to whether last good DBCC got updated or not except, of course, ESTIMATEONLY because it specifically does not run any consistency checks.