Finding the last successful CHECKDB
One very important part of your SQL Server maintenance procedure should be running consistency checks on a regular basis using CHECKDB. Not only does CHECKDB check the health of important internal pages of your database like PFS, GAM, IAM and many more, it also check for errors in your data pages (and it does a lot more than just checking pages!). As you – hopefully – know, running CHECKDB is really important to make sure your database is consistent and uncorrupted.
There are however scenarios where you have to manage a database and you are not sure when, or if, CHECKDB completed successfully. Thankfully we can find the date and time the last successful CHECKDB completed, though it isn’t as easy as it should be…
Undocumented fun
What I mean with finding the last successful CHECKDB date/time not being easy is that we need to use an undocumented DBCC command to get that data back from the database. Undocumented usually means fun, so let’s get started!
The information we are after is stored in the boot page of a database.
Every database has one boot page and it is always in the same location, the 9th page in the first file of the database. The boot page stores important information about the database like the database name, database creation date, the compatibility of the database and much more interesting stuff. It also contains the date and time of the last successful CHECKDB that has ran on that specific database!
When we are talking about getting information back from pages we need to use the undocumented DBCC PAGE command. To get the contents of the boot page we can execute the command below:
DBCC PAGE (databasename, 1, 9, 3) WITH TABLERESULTS
I’m using the WITH TABLERESULTS command so we do not have to set TF 3604 and TF 3605 to output the page information.
The information we are looking for is in the field ‘dbi_dbccLastKnownGood’ as that shows us the last successful CHECKDB run.
In this case I have ran the command against the AdventureWorks2012 database on my test machine:
On my test machine CHECKDB had the last successful run on 2014-05-30 at 11:56.
If you see a date of ‘1900-01-01 00:00:00.000’ it means CHECKDB has either never ran, or – even worse – has never had a successful run.
I hope you noticed I used the word successful a lot in this article, this is because the date and time in the boot page is really the last successful run. If CHECKDB encountered an error during execution the ‘dbi_dbccLastKnownGood’ value will not change in the boot page!