[Edit: From 2016 SP2 onward, you can use SELECT DATABASEPROPERTYEX (‘dbname’ , ‘LastGoodCheckDbTime’ ); instead]
This came up several times during the week so I thought it was about time to blog about it. One of the new features we put into SQL Server 2005 was storing the last time that DBCC CHECKDB completed successfully (called the last-known good time). It’s the time that the DBCC CHECKDB *started*, not completed.
What does successfully mean? This is the catch – if DBCC CHECKDB runs to completion then it considers that a successful run – EVEN if it found some corruptions. However, the last-known good time is ONLY updated if there were NO corruptions found. Slightly confusing I know.
Cool – but how can you see it? Well, the only time it’s ever reported is when the database starts up. Not too useful if the database has been running for months. So how to see it??? The trick is to use DBCC PAGE. The last-known good time is stored in the boot page of the database – page 9. The following code will dump page 9 for you:
— you need this to get any DBCC PAGE output
DBCC TRACEON (3604);
GO— page 9 is the boot page
DBCC PAGE (dbname, 1, 9, 3);
GO
You need to look for the dbi_dbccLastKnownGood field. That was the last time that DBCC CHECKDB ran without finding any corruptions.
Now – what about if you’re trusting your SQL Agent jobs to run DBCC CHECKDB for you every so often (how often is a whole other discussion…) and relying on the Agent job failing if DBCC CHECKDB finds corruptions. An Agent job WILL fail if a DBCC CHECKDB within it finds corruptions because DBCC CHECKDB will set the @@ERROR. Now here is a real issue – the Job History that’s captured will not contain all the output from DBCC CHECKDB – especially if you didn’t use the WITH NO_INFOMSGS option. You should make sure you capture the output to a file to avoid having to go back and run DBCC CHECKDB all over again.
Summary – make sure you really know when DBCC CHECKDB runs successfully, without finding any corruptions!
21 thoughts on “CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?”
Great stuff as always, Paul. Thanks for the clarification on this behavior.
For what it’s worth, you can also get at dbi_dbccLastKnownGood via DBCC DBINFO (‘dbname’) WITH TABLERESULTS. This might be preferable in some cases to setting a trace flag.
Thanks again!
-wp
Methouds listed above are applicable to SQL 2005 only, any clues for SQL 2000 ?
No – there’s no way to tell in 2000 except to look back through the SQL Server error logs for the DBCC CHECKDB completion messages.
For newly created databases (with no checkdb done) I found that the value for dbi_dbccLastKnownGood is the same as the value for the model database.
In a way this is correct, because a new database is a copy of the model database. On the other hand the checkdb is not executed on the new database itself and therefore a healthy database is not guaranteed
What is your opinion about this?
regards, Robbert
Paul,
How do we find the last good CheckDB for a readonly database. I still like to run checkdb against these, since physical corruption could occur, but dbi_dbccLastKnownGood doesn’t appear to update if the database is readonly. Have you given any thought to storing this value in master as well, where it could be updated for both readwrite and readonly databases?
Thanks,
Dave
You’d have to look through the error logs to find that – or switch the database to read/write before doing the CHECKDB. I strongly suspect that MS won’t make any changes to where to store the value, but go ahead and open a Connect item to suggest it if you want.
Paul,
How do i know last good DBCC check table done? I have huge database and i am splitting the check using Checktable across different tables. Thanks.
It only tracks CHECKDB executions. You’d have to search the error log for any other CHECK* commands.
Hi Paul,
I have query.
When DBCC CHECKDB updates the dbi_dbccLastKnownGood field, what would be the actual timestamp updated? I mean is it the start time of DBCC CHECKDB OR would it be the completion time of DBCC CHECKDB?
Thanks.
It’s the time when the DBCC CHECKDB started. I’ll update the post to make that clear.
This is the catch – if DBCC CHECKDB runs to completion then it considers that a successful run – EVEN if it found some corruptions. However, the last-known good time is ONLY updated if there were NO corruptions found!!
Here it means ONLY db is corruption free , is this value updated. Completely perplexed now
What’s confusing? Last-known-good is when CHECKDB last ran without finding corruptions, not when it last run to completion.
I am not sure if anyone has run into this but it has me stumped.
Instance is running SQL Server 2012 (SP4-GDR), Enterprise Edition
I ran a DBCC CheckDB on a database on April 13, 2020 and the SQL Server Log is reporting: found 0 errors and repaired 0 errors. The last-known-good value I see is from 2015.
I am not manually running against a snapshot located on a disk separate from the database (I’ve read that this can cause the value not to update) so I am a bit stumped. Either there is an issue with the last-known-good value getting updated which seems unlikely because other databases on this instance are seeing theirs updated without issue, or the log file is suspect.
Due to this conflicting information I am not actually sure if my database is corruption free or not.
I haven’t heard of any issues with updating the last-known good. How are you examining the last known good value?
Sanity checked it using DBCC DBINFO (‘dbname’) WITH TABLERESULTS after some strange behavior from a DBCC run over the weekend.
I was refreshing my progress tracking script for the DBCC CheckDB, watched it hit 99.9%, refreshed, and it appeared to have restarted and dropped back to 0% where it stayed without progressing until we killed it early Monday.
I ended up stuck on the conflicting info between the logs and the dbinfo lastknowngood values after poking around to see if it had actually completed the check or not.
Hard to say what the issue was – possibly a problem dropping the snapshot. If it printed the completion message in the error log stating no problems found, then the checks definitely completed with no issues.
SELECT DATABASEPROPERTYEX (‘dbname’ , ‘LastGoodCheckDbTime’ ) now returns the same 2016 SP2 onwards
Thanks – updated.
Thanks – post updated
Thanks – post updated.
Thanks – I updated the post.