Search This Blog

Thursday, June 13, 2024

DBCC CHECKDB Last Run Query for Availability Group CHECKDB Monitoring

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: