DBCC CHECKDB Last Run Query for Availability Group CHECKDB Monitoring

Running DBCC CHECKDB on every production database is a best practice, some would call it imperative for preventive maintenance. Exceptions exist for identical database copies across servers, but only if you're certain the secondary is disposable and never used for production. Unless that's the case, run CHECKDB everywhere to catch corruption early.

SQL Server 2016 SP2 added LastGoodCheckDbTime to DATABASEPROPERTYEX, tracking the last successful DBCC CHECKDB run (completes without errors). Updates when: Full DBCC CHECKDB succeeds; DBCC CHECKFILEGROUP on all filegroups; even PHYSICAL_ONLY mode. Doesn't update when: DBCC CHECKTABLE (table-level only); ESTIMATEONLY; or CHECKDB finds/skips corruption.

Here is an example SQL statement to demo it:

SELECT DATABASEPROPERTYEX('model', 'LastGoodCheckDbTime') AS [LastGoodCheckDbTime]

This time  lets get LastGoodCheckDbTime for all databases: 

/*
Works on:
SQL Server 2019 and up
SQL Server 2016 SP2 and up
SQL Server 2017 CU9 and up
 
Note: For databases that are part of an Availability Group,
      LastGoodCheckDbTime will return the date and time of 

      the last successful DBCC CHECKDB that ran on the 

      primary replica, regardless of which replica you run 

      the command from.
 
Reference:

https://learn.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql

*/

SELECT
    GETDATE() AS [CurrentTime],
    @@SERVERNAME AS [SQLServer],
    @@VERSION AS [SQLVersion],
    d.name AS [Database],
       dbrs.is_primary_replica [IsPrimaryReplica],
    DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS [LastGoodCheckDbTime],
    DATEDIFF(D, CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime')
             AS DATETIME), GETDATE()) AS [DaysSinceLastCheckDB],
    d.user_access_desc AS [UserAccessMode],
    d.is_read_only AS [IsReadOnly],
    d.state_desc AS [Status],
    d.recovery_model_desc AS [RecoveryModel],
    'DBCC CHECKDB ([' + d.name + '])
       WITH PHYSICAL_ONLY, NO_INFOMSGS, ALL_ERRORMSGS;' AS [SQLCheckDB]
FROM sys.databases d

left join sys.dm_hadr_database_replica_states dbrs
on dbrs.replica_id = d.replica_id
and dbrs.group_database_id = d.group_database_id
ORDER BY d.name;

  

As you can see in the following partial screen shot, some databases have never had the DBCC CHECKDB performed, for some others it has been a long while. This is not acceptable.

What can I do with this information?

For one thing, I want to immediately look for suitable time to schedule DBCC CHECKDB for databases that have not been checked in last X number of days, for example 7 days. For example:

DBCC CHECKDB ([AdventureWorks])  WITH PHYSICAL_ONLY, NO_INFOMSGS, ALL_ERRORMSGS;

Second, I will schedule an alert to get notified if a database has not been checked in the last X number of days. My preferred method would be to schedule a PowerShell script once a day that will check all databases on all SQL Servers and send me a nice email report.

Third, I can incorporate this into my DBCC CHECKDB job to either prioritize which databases to perform CHECKDB on first and/or selectively perform CHECKDB on databases that have not been checked in X number of days.

I am sure there are other use cases, like performing integrity checks just prior to doing database backups to make sure we are not backing up a database with corruption issues etc.

References:

https://learn.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql

---

Other Methods

There are other, legacy methods exists too, DBCC DBINFO and DBCC PAGE methods comes to mind for pre SQL 2016 SP2 versions. Fall back to these DBCC methods only for legacy servers.

Method 2: Using DBCC DBINFO

DBCC DBINFO('MSDB') WITH TABLERESULTS

Method 3: Run DBCC PAGE against the database boot page

DBCC TRACEON(3604);

GO

-- Page 9 = boot page, file 1

DBCC PAGE ('msdb', 1, 9, 3);

GO

DBCC TRACEOFF(3604);

GO

 

-- Automate DBCC PAGE

DBCC TRACEON(3604);

GO

 

CREATE TABLE #BootPage (

    ParentObject varchar(255),

    [Object]     varchar(255),

    Field        varchar(255),

    [Value]      varchar(255)

);

 
INSERT #BootPage
EXEC ('DBCC PAGE (''msdb'', 1, 9, 3) WITH TABLERESULTS, NO_INFOMSGS;');

 

SELECT [Value] AS LastGoodCheckDB
FROM #BootPage
WHERE Field = 'dbi_dbccLastKnownGood';
 
DROP TABLE #BootPage;
 
DBCC TRACEOFF(3604);
GO

Resources:

Get Last DBCC CHECKDB Date Using DBCC DBINFO

Get Last DBCC CHECKDB Date Using DBCC PAGE