Check your CHECKDBs

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

DBCC DBINFO('AdventureWorks2017') WITH TABLERESULTS;

• DATABASEPROPERTYEX method, compatible with SQL Server 2016 SP2 onwards

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

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
ON
(
NAME = AdventureWorks2017,
FILENAME = 'C:\SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\
AdventureWorks2017_CheckDBSnapshot.ss'
)
AS SNAPSHOT OF [AdventureWorks2017];
GO

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

DBCC CHECKDB('AdventureWorks2017_CheckDBSnapshot') WITH NO_INFOMSGS, ALL_ERRORMSGS;

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

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

DBCC CHECKDB('AdventureWorks2017') WITH NO_INFOMSGS, ALL_ERRORMSGS;

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

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.