DBCC CHECKDB Last Run Query for Availability Group CHECKDB Monitoring

DBCC CHECKDB checks the logical and physical integrity of a database, including allocation structures, tables, indexed views, and catalog consistency. It is one of the core preventive maintenance tasks, yet it often gets less attention than index and statistics maintenance. Many DBAs run index maintenance nightly, albeit with dynamic thresholds, but run CHECKDB only once a week. I won't debate the optimal frequency here; Brent Ozar's DBCC CHECKDB FAQ covers it well, noting that weekly is common and daily is often chosen when availability matters.

My purpose is to answer one simple question:

When did this database last complete a successful integrity check?

Starting with SQL Server 2016 SP2 and SQL Server 2017 CU9, DATABASEPROPERTYEX exposes the LastGoodCheckDbTime property. It is a convenient monitoring signal for finding databases that may be overdue for a successful DBCC CHECKDB.

Keep a few limitations in mind from the start:

• It is only a timestamp. It doesn't tell you the CHECKDB options, output, duration, or where it ran. If those details matter, keep job history and centralized logging.

• In an Availability Group, it reflects the primary replica. The value shows the last successful CHECKDB on the primary, regardless of which replica you query. Checks run on a secondary aren't reflected, and each replica has its own storage to verify.

• 1900-01-01 means never checked. If CHECKDB has never run on the database, the property returns 1900-01-01 00:00:00.000.

• NULL means investigate. DATABASEPROPERTYEX returns NULL for invalid input or when a database hasn't started, such as one that is offline or closed by AUTO_CLOSE. A NULL value is a signal to look further, not evidence about CHECKDB history.

---

What updates LastGoodCheckDbTime?

The value is the start time of the last DBCC CHECKDB that completed without finding corruption. Community testing shows a few behaviors worth knowing:

Updates the value:

• DBCC CHECKDB that completes with no corruption errors

• DBCC CHECKDB WITH PHYSICAL_ONLY, even though logical checks are skipped

• DBCC CHECKFILEGROUP, even on a single filegroup

Does not update the value:

• DBCC CHECKTABLE, CHECKALLOC, or CHECKCATALOG, even if you run all of them to split up a CHECKDB

• DBCC CHECKDB that reports corruption errors

• Checks on READ_ONLY databases

• Checks run against a manually created database snapshot

• Checks run on an Availability Group secondary replica

• WITH ESTIMATEONLY, which estimates tempdb space without running the checks

Because PHYSICAL_ONLY and single-filegroup checks also update the timestamp, a recent value doesn't prove a full logical check covered the whole database. These behaviors are based on community testing, not Microsoft documentation, so verify them on your SQL Server version.

---

Here is an example SQL statement to demo it:

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

This time  lets get LastGoodCheckDbTime for all databases: