+44 (0)20 3051 3595 | info@coeo.com | Client portal login

Check your CHECKDBs

Ben Edwards

To find out when the last successful DBCC CHECKDB for a given database was, we can use the DBCC DBINFO() command and look for the dbi_dbccLastKnownGood value. Alternatively, Microsoft introduced this information in the form of a DATABASEPROPERTYEX value, starting from SQL Server 2016 SP2:

  • Traditional Method, look for dbi_dbccLastKnownGood value

  • DATABASEPROPERTYEX method, compatible with SQL Server 2016 SP2 onwards
SELECT DB_NAME() AS [Database], DATABASEPROPERTYEX ('AdventureWorks2017' , 
'LastGoodCheckDBTime' ) AS [Last Good CHECKDB Time];

Last successful CHECKDB

Recently, our proactive monitoring alerted us to a server which identified a number of databases that hadn't received a successful integrity check in over 14 days. We would usually see a warning like this when either an Integrity Check job had been failing, or a new database had been restored. But this time, it was not the case.

I found that a change had been made to the integrity check processes, meaning that they were now being offloaded to a separate drive, using a manually created database snapshot. While this is a valid method (especially when you have an extremely busy server) - it has a lesser known side effect when looking at the dbi_dbccLastKnownGood value.

To demonstrate this behaviour, we can run the following code against the AdventureWorks2017 database:

  • Firstly, we manually create our database snapshot:
CREATE DATABASE AdventureWorks2017_CheckDBSnapshot
NAME = AdventureWorks2017,
AS SNAPSHOT OF [AdventureWorks2017];

  • We then execute the DBCC CHECKDB command against the AdventureWorks2017 snapshot and can see that the “Last Good CHECKDB Time” hasn’t changed.

SELECT DB_NAME() AS [Database], DATABASEPROPERTYEX ('AdventureWorks2017' , 
'LastGoodCheckDBTime' ) AS [Last Good CHECKDB Time];


Screen command snapshot result


  • Finally, we run the CHECKDB against the source database and can see that this updates the value successfully.


SELECT DB_NAME() AS [Database], DATABASEPROPERTYEX ('AdventureWorks2017' ,
'LastGoodCheckDBTime' ) AS [Last Good CHECKDB Time];


Updated CHECKDB value

So why did this happen? The dbi_dbccLastKnownGood value is stored within the database header and is updated after a successful DBCC CHECKDB completes. As the check is now completing against a database snapshot, the file is static and more importantly a read-only view of the database at the time of its creation.

In short, this behaviour means that whenever manual database snapshots are used for DBCC CHECKDB purposes, the source database is unaware that the check has taken place. You must therefore adjust any monitoring or code that relies on this metadata being updated. One way which you can avoid this issue is to suppress any monitoring used for a CHECKDB being required and monitoring the Integrity Check job for failures.

Develop your SQL Server knowledge - learn from Coeo's experts

Subscribe to Email Updates

Back to top