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
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
- How Much Log Can a Backup Log
- The Case of the Backups That Wouldn’t Restore
- 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:bakTestFile.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.
Day 23 of 31 Days of Disaster Recovery: Restoring Differential Backups With New Files | SQLSoldier
[…] Which DBCC CHECK Commands Update Last Known Good DBCC […]
Day 25 of 31 Days of Disaster Recovery: Improving Performance of Backups and Restores | SQLSoldier
[…] Which DBCC CHECK Commands Update Last Known Good DBCC […]
Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database | SQLSoldier
[…] Which DBCC CHECK Commands Update Last Known Good DBCC […]