Mission: SQL Homeostasis: DBCC CHECKDB Last Run Query for Availability Group CHECKDB Monitoring

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:

/*
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);

-- Page 9 = boot page, file 1

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

DBCC TRACEOFF(3604);

-- Automate DBCC PAGE

DBCC TRACEON(3604);

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

Mission: SQL Homeostasis

Search This Blog

Thursday, June 13, 2024