Last Known Good CheckDB

Comments: 11 Comments
Published on: November 20, 2012

Diligent DBAs like to check their databases for a little thing called corruption.  Some of those DBAs are masterful at keeping records and logging that the database was checked and came up with a clean bill of health.

There are many different ways of logging this kind of activity.  Today I will share one such way to track when the last successful run of Checkdb happened.

First a little back story

A question came across twitter on the SQLhelp hashtag. The question was “Does restoring a database clear the dbi_dbccLastKnownGood value on the boot page?”

This question prompted me to do a quick test to see.  The test is simple.  Create a small database, backup the database, run checkdb, check to see what the dbi_dbccLastKnownGood value is, restore the previous backup and check the dbi_dbccLastKnownGood value again.

So here is a quick script

[codesyntax lang=”tsql”]


If you run this little test, you should observe that the date value for dbi_dbccLastKnownGood changes with each test.  Now let’s discuss the question and the answer to that original question about whether or not this value gets cleared.

The value does not get cleared.  The value does not get reset.  The cause for the change in the value that you have observed is due simply to the boot page having been restored.  If CheckDB has never been run on the database, you will get the SQL default date of ‘1900-01-01 00:00:00.000’.

And then…

Now that the back story is told, that brings us to how to track this – at least one such method.  I had to cover the back story since it is what prompted the writing of a method to gather this information in a quick script so I could use it to monitor.  Yes, it is just another tool to throw into the toolbox.  And to reiterate, it is by no means the only way to track or gather this type of information.  Some are more elaborate than others.  It is up to you to choose.

Keeping in mind that we can get the last time that Checkdb completed without a report of corruption, I delve into this cursor based method to retrieve the dbi_dbccLastKnownGood value for all databases within your SQL Server Instance.

[codesyntax lang=”tsql”]


You can use this or find something else if you desire.  The sole purpose of this script is to provide a means to gather quickly the last date known to report a clean bill of health from Checkdb.  From there, I will leave it to you to add it to another process in order to track over the long term.

You can read the twitter conversation here.  I also have it on good authority that Bob Pusateri (twitter) will be blogging about the same topic.

11 Comments - Leave a comment
  1. […] Back in November 2012, I shared a story about checking the last known good checkdb in the boot page of a database.  You can read that story here. […]

  2. Robert Every says:

    T-SQL code you shared at
    is EXACTLY what I need! Thanks a whole bunch.

    I’d like to add you to my professional network on LinkedIn but will not select “Colleague,” “Friend,” or other false radio button (because it’s just not right).
    – Robert Every

  3. Geoff Ruble says:

    This is great! Thank you!!!

  4. Yoda says:

    There is a simpler way without the need for cursors.

    Id INT IDENTITY(1,1),
    ParentObject VARCHAR(255),
    [Object] VARCHAR(255),
    Field VARCHAR(255),
    [Value] VARCHAR(255)


    ;WITH DbInfo AS
    [Value] AS DatabaseName,
    ROW_NUMBER() OVER (ORDER BY ID) AS RowNum1 FROM @a WHERE Field IN (‘dbi_dbname’)),
    LKG AS ( SELECT [Value] AS LastKnownGood_CHECKDB, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum2 FROM @a WHERE Field IN (‘dbi_dbccLastKnownGood’)

    GETUTCDATE() AS SessionDateTimeUTC,

    FROM DbInfo
    LEFT OUTER JOIN LKG ON RowNum1 = RowNum2

    • Jason Brimhall says:

      Actually, this method relies explicitly on a buggy cursor. SP_MSFOREACHDB comes with inconsistencies and a few bugs. The heart of this proc is indeed a cursor. I recommend to use the original script I posted many times over the SP_MSFOREACHDB method.

  5. […] this new thing called Google. My colleague Jason Hall sent me a script that he modified from this excellent post on the […]

  6. […] for each database. When you look for what you can run to find when CHECKDB was last run you find this blog post and also this blog post on grabbing this info. While these were very informative, they were for one […]

  7. […] for each database. When you look for what you can run to find when CHECKDB was last run you find this blog post and also this blog post on grabbing this info. While these were very informative, they were for […]

  8. […] I ran into a proof of concept blog written by Jason Brimhall discussing issues involving dbi_dbccLastKnownGood and database restore […]

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

November 2012
« Oct   Dec »

Welcome , today is Wednesday, February 19, 2020