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

31 Days of Disaster Recovery 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:

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.

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.