SQL Server DBCC CHECKDB Last Run Date Report

calendar Apr 17, 2026 / · 7 min read · sql server dbcc commands database maintenance database integrity DBCC CHECKDB DBCC DBINFO dba scripts database administration sql server monitoring integrity check  ·
Share on: linkedin copy

Running DBCC CHECKDB on every database is one of the most important tasks for a SQL Server DBA. But in busy environments it is easy to lose track of which databases have been checked recently and which have not. This script loops through all user databases, reads the last known good DBCC CHECKDB date from each one, and presents the results sorted from oldest to newest so you can spot gaps fast.

Purpose and Overview

SQL Server stores the date and time of the last successful DBCC CHECKDB run inside each database's boot page. The internal field name is dbi_dbccLastKnownGood. You can read this field by running DBCC DBINFO() WITH TABLERESULTS against each database and filtering for that field name.

This script automates that process across all user databases. It builds a temp table, loops through every non-system database using a cursor, runs DBCC DBINFO() for each one, captures the last known good date, and then returns a single result set ordered by that date ascending. The databases that have gone the longest without a check appear at the top.

This makes the script useful as a quick audit tool, as a scheduled report, or as a first step when investigating data integrity concerns.

Code Breakdown

 1SET NOCOUNT ON;
 2
 3-- Create temp table to hold results
 4CREATE TABLE #CheckDBResults (
 5    DatabaseName    NVARCHAR(128),
 6    LastKnownGood   DATETIME
 7);
 8
 9-- Temp table to hold DBCC DBINFO output
10CREATE TABLE #DBInfo (
11    ParentObject    NVARCHAR(255),
12    Object          NVARCHAR(255),
13    Field           NVARCHAR(255),
14    Value           NVARCHAR(255)
15);
16
17DECLARE @DatabaseName NVARCHAR(128);
18DECLARE @SQL          NVARCHAR(500);
19
20-- Cursor over all online user databases
21DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
22    SELECT name
23    FROM sys.databases
24    WHERE database_id > 4          -- exclude system databases
25      AND state_desc = 'ONLINE'
26    ORDER BY name;
27
28OPEN db_cursor;
29FETCH NEXT FROM db_cursor INTO @DatabaseName;
30
31WHILE @@FETCH_STATUS = 0
32BEGIN
33    -- Clear previous iteration results
34    TRUNCATE TABLE #DBInfo;
35
36    -- Run DBCC DBINFO for this database
37    SET @SQL = N'DBCC DBINFO([' + @DatabaseName + N']) WITH TABLERESULTS, NO_INFOMSGS;';
38
39    BEGIN TRY
40        INSERT INTO #DBInfo (ParentObject, Object, Field, Value)
41        EXEC (@SQL);
42
43        -- Extract the last known good CHECKDB date
44        INSERT INTO #CheckDBResults (DatabaseName, LastKnownGood)
45        SELECT @DatabaseName,
46               CASE
47                   WHEN Value = '1900-01-01 00:00:00.000' THEN NULL
48                   ELSE CONVERT(DATETIME, Value, 121)
49               END
50        FROM #DBInfo
51        WHERE Field = 'dbi_dbccLastKnownGood';
52    END TRY
53    BEGIN CATCH
54        -- If DBCC DBINFO fails for any database, record it with NULL
55        INSERT INTO #CheckDBResults (DatabaseName, LastKnownGood)
56        VALUES (@DatabaseName, NULL);
57    END CATCH;
58
59    FETCH NEXT FROM db_cursor INTO @DatabaseName;
60END;
61
62CLOSE db_cursor;
63DEALLOCATE db_cursor;
64
65-- Return results, oldest check date first (NULLs = never checked appear at top)
66SELECT
67    DatabaseName,
68    LastKnownGood,
69    CASE
70        WHEN LastKnownGood IS NULL THEN 'Never checked'
71        ELSE CAST(DATEDIFF(DAY, LastKnownGood, GETDATE()) AS NVARCHAR(10)) + ' days ago'
72    END AS DaysSinceLastCheck
73FROM #CheckDBResults
74ORDER BY LastKnownGood ASC;
75
76-- Cleanup
77DROP TABLE #CheckDBResults;
78DROP TABLE #DBInfo;

Temp Tables

Two temp tables are created at the start. #DBInfo holds the raw output from DBCC DBINFO(), which returns many rows covering various database boot page fields. #CheckDBResults holds the single row per database that we care about: the database name and its last known good CHECKDB date.

Cursor Over User Databases

The cursor queries sys.databases and excludes the four built-in system databases by filtering database_id > 4. It also filters for state_desc = 'ONLINE' so that offline, restoring, or suspect databases do not cause errors during the loop.

DBCC DBINFO with TABLERESULTS

DBCC DBINFO() WITH TABLERESULTS is an undocumented but widely used command that returns database boot page information in a tabular format with columns ParentObject, Object, Field, and Value. The NO_INFOMSGS option suppresses the extra informational messages that would otherwise appear in the messages pane.

Dynamic SQL is used here because DBCC DBINFO does not accept a variable directly as the database name argument — the database name must be part of the executed string.

Extracting dbi_dbccLastKnownGood

After inserting the DBCC DBINFO output into #DBInfo, the script filters for Field = 'dbi_dbccLastKnownGood' to pull the last known good date. A CASE expression converts the SQL Server default zero-date value (1900-01-01 00:00:00.000) to NULL, which makes it clear that CHECKDB has never been run on that database rather than showing a misleading 1900 date.

TRY/CATCH for Error Handling

Each database is wrapped in a TRY/CATCH block. If DBCC DBINFO fails for any reason — for example, insufficient permissions on a specific database — the catch block inserts a NULL row for that database rather than stopping the entire script.

Final Result Set

The final SELECT returns three columns: the database name, the last known good date, and a computed DaysSinceLastCheck column showing either "Never checked" or the number of days since the last successful run. Results are ordered ascending by LastKnownGood so that NULL values and the oldest dates appear at the top of the list.

Key Benefits and Use Cases

  • Quickly identify any database where DBCC CHECKDB has never been run or has not run in a long time
  • Use as a pre-maintenance audit before major changes such as upgrades or migrations
  • Run as a scheduled SQL Server Agent job that sends results by email or writes to a logging table
  • Include in a broader DBA health check report alongside backup history and index fragmentation data
  • Verify that your maintenance plan or Ola Hallengren maintenance solution is executing CHECKDB on schedule
  • Immediately surface any new databases that have been added but not yet included in a maintenance schedule

Performance Considerations

DBCC DBINFO() reads only the database boot page — it does not scan data files or perform any integrity checking. It is a very lightweight operation that completes in milliseconds per database, so running this script against an instance with many databases has minimal performance impact.

The cursor approach executes one statement per database sequentially. On an instance with a very large number of databases (hundreds or more), consider whether a scheduled Agent job or off-peak execution is appropriate, though the total runtime is still typically measured in seconds.

This script does not replace running DBCC CHECKDB itself. It only reads the stored date of the last successful run. If CHECKDB has never been run, or if it was last run before the current database was restored from a backup, the date may not reflect the actual integrity state of the data files as they exist now.

Note that DBCC DBINFO is an undocumented command. Microsoft does not guarantee its output format will remain the same across SQL Server versions. The dbi_dbccLastKnownGood field has been stable across SQL Server 2005 through SQL Server 2022, but always test on a new version before relying on it in production tooling.

Practical Tips

Schedule it as an Agent job. Set up a SQL Server Agent job that runs this script weekly and inserts the results into a permanent audit table. Over time you build a history of CHECKDB compliance across all databases.

Add an alert threshold. Extend the final SELECT with a WHERE DaysSinceLastCheck > 7 filter (or whatever your SLA requires) to return only the databases that are overdue. This makes the output immediately actionable.

Combine with backup history. Pair this report with the backup history script to get a complete picture of database health: when was the last backup, and when was the last integrity check.

Use with Ola Hallengren's solution. If you are already using Ola Hallengren's DatabaseIntegrityCheck job, this script is a useful way to verify that every database is included and running on schedule.

Check after a restore. When you restore a database from backup onto a new server, the dbi_dbccLastKnownGood value carries over from the source. Running DBCC CHECKDB on the restored database resets this value on the destination, confirming the restored copy is clean.

Conclusion

Knowing when DBCC CHECKDB last ran on every database is a basic part of SQL Server database administration. This script provides a fast, low-impact way to audit the entire instance in one pass. Run it regularly to make sure no database falls through the gaps, and combine it with a solid maintenance schedule to keep your data integrity checks on track.

References

Posts in this series