DBA Scripts: Get Last DBCC CHECKDB

Part of the DBA-Tools Project.

---

DBCC CHECKDB is the only thing that actually confirms a database’s data is structurally sound; backups only prove a database can be restored, not that what gets restored is undamaged. Skipping it, or letting it silently stop running, means corruption can sit undetected for weeks or months before anyone notices, often only when a query fails or a restore reveals the damage was there all along.

This script checks every user database’s last successful CHECKDB completion time in a single query, no need to dig through job history or check each database individually.

---

Why Last DBCC CHECKDB Matters

Corruption doesn’t always announce itself immediately. A damaged page might sit untouched for a long time before a query happens to read it:

• CHECKDB is the only routine check that verifies structural and allocation integrity, not just that a backup file exists
• Corruption caught early is usually fixable from a recent clean backup; corruption caught late may mean every recent backup is also corrupt
• A NEVER_RUN result on a production database is a real gap, not a formality
• Microsoft’s own guidance is weekly at minimum; this script makes it trivial to confirm that’s actually happening

---

When to Run This Script

• Routine SQL Server health checks
• Auditing a server or estate you’ve just inherited
• Investigating a suspected corruption issue or an unexplained restore failure
• Confirming maintenance jobs are actually running, not just scheduled

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-LastDbccCheckdb
Category    : maintenance-and-reliability
Purpose     : Show when each user database last had a successful DBCC CHECKDB run.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-last-dbcc-checkdb/)
Requires    : VIEW ANY DATABASE
Notes       : Uses DATABASEPROPERTYEX('LastGoodCheckDbTime') — available SQL Server 2012+.
              NULL means CHECKDB has never completed successfully on this instance for that database.
              Microsoft recommends running CHECKDB at least weekly.
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    d.name                                                                  AS database_name,
    d.state_desc,
    d.recovery_model_desc,
    CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS DATETIME)    AS last_good_checkdb,
    DATEDIFF(DAY,
        CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS DATETIME),
        GETDATE())                                                           AS days_since_checkdb,
    CASE
        WHEN CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS DATETIME) IS NULL
            THEN 'NEVER_RUN'
        WHEN DATEDIFF(DAY,
                CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS DATETIME),
                GETDATE()) > 7
            THEN 'STALE'
        ELSE 'OK'
    END                                                                     AS checkdb_status
FROM sys.databases AS d
WHERE d.database_id > 4
ORDER BY last_good_checkdb ASC;

The script reads DATABASEPROPERTYEX(..., 'LastGoodCheckDbTime') for every online user database and classifies each as OK, STALE (over 7 days), or NEVER_RUN.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check last successful DBCC CHECKDB per database:
.\run.ps1 Get-LastDbccCheckdb

# To run against a remote sql server:
.\run.ps1 Get-LastDbccCheckdb -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/databases/Get-LastDbccCheckdb.sql
• powershell/wrappers/monitoring/databases/Get-LastDbccCheckdb.ps1

---

Example Output

Every database on this lab instance is OK, all checked within the last 4 days.

---

Understanding the Results

• last_good_checkdb — the timestamp of the most recent successful completion. NULL means it’s never completed successfully on this database, on this instance.
• days_since_checkdb — the primary thing to sort by. Anything climbing well past your maintenance schedule’s expected interval is worth investigating why.
• checkdb_status — NEVER_RUN is the highest priority; STALE (over 7 days by this script’s default) means the schedule may have stopped running or is misconfigured.
• A database that was recently restored or attached shows NEVER_RUN until CHECKDB runs against it at least once on this instance; that’s expected, not necessarily a problem.

---

How to Fix a Stale or Missing CHECKDB Schedule

-- Run CHECKDB manually against a specific database
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

For ongoing coverage, schedule it through SQL Server Agent or Ola Hallengren’s maintenance solution rather than relying on manual runs. sql/maintenance/Generate-IndexMaintenanceScript.sql and the repo’s collector jobs cover related scheduled maintenance if a proper job doesn’t already exist.

---

Best Practices

• Schedule CHECKDB at least weekly on every production database; more often for critical, high-change databases.
• Treat a NEVER_RUN result on a database that’s been in production for a while as urgent, not routine.
• Run CHECKDB before and after major maintenance (index rebuilds, upgrades, migrations) to establish a clean baseline.
• If CHECKDB runtime is a problem on large databases, consider WITH PHYSICAL_ONLY for more frequent lightweight checks between full runs, not as a permanent replacement.

---

Related Scripts

You may also find these scripts useful:

• Database Health
• Database Integrity Checks
• Suspect Pages

---

Frequently Asked Questions

Does a recent backup mean the database is free of corruption?

No. A backup only proves the database can be restored, not that its data pages are structurally sound. CHECKDB is the actual integrity check; backups and integrity checks answer different questions.

How often should CHECKDB actually run?

Weekly at minimum, per Microsoft’s own guidance. Larger or more critical databases often warrant more frequent checks, balanced against the resource cost of running it.

---

Summary

CHECKDB is the one maintenance task that actually answers “is this data sound,” and it’s exactly the kind of thing that quietly stops running without anyone noticing until it matters.

Run this script as part of routine health checks, and treat any NEVER_RUN or long-STALE result on a production database as something to fix immediately, not queue for later.