Quick Scan Report – DBCC CheckDB Not Run Recently

What this check looks for

Databases whose last known good CHECKDB date is more than 30 days old, and is not null. The null case is the separate and more serious “never run” check.

The date comes from the database’s boot page, where SQL Server records dbi_dbccLastKnownGood when a CHECKDB passes cleanly.

Why it matters

This is a check that used to pass and now does not, which makes it more informative than it looks. Somebody set integrity checking up. It ran. And then it stopped, and nobody noticed, which means whatever was supposed to tell you it stopped is also not working.

The usual causes are all things worth knowing about independently:

• The job is failing. Often on one large database, and because the built in maintenance plan task handles all databases in one step, a failure partway through means every database after it in the list is skipped too. That has its own check.
• The job was disabled for a maintenance window and never re-enabled.
• The window got too small. The database grew, CHECKDB now takes longer than the window allows, and it is being killed each night. A killed CHECKDB records nothing, so the date stops moving while the job appears to run.
• The database was restored from elsewhere. The date travels with the database, so a database restored from a server that was not checking it arrives already stale.

The exposure is measured against your backup retention. If corruption occurred a week after the last successful check, and your retention is four weeks, the last clean backup is already gone and nobody knows. Thirty days without verification on a database with two weeks of backups means there is no window in which you can be sure of a clean restore.

30 days is a deliberately lenient threshold. Weekly is the usual recommendation, and many shops run it nightly. A database that has gone a month is not slightly overdue.

How to confirm it yourself

Per database:

DBCC DBINFO ('YourDatabase') WITH TABLERESULTS;   -- read dbi_dbccLastKnownGood

Across the instance:

CREATE TABLE #dbcc (ParentObject VARCHAR(255), [Object] VARCHAR(255),
                    Field VARCHAR(255), [Value] VARCHAR(255), DbName VARCHAR(255) NULL);

EXEC sp_MSforeachdb N'
    IF ''?'' NOT IN (''tempdb'')
    BEGIN
        INSERT INTO #dbcc (ParentObject, [Object], Field, [Value])
        EXEC (''DBCC DBINFO([?]) WITH TABLERESULTS, NO_INFOMSGS'');
        UPDATE #dbcc SET DbName = ''?'' WHERE DbName IS NULL;
    END';

SELECT DbName,
       [Value]                                          AS [last_known_good],
       DATEDIFF(DAY, CONVERT(DATETIME, [Value]), GETDATE()) AS [days_ago]
  FROM #dbcc
 WHERE Field = 'dbi_dbccLastKnownGood'
   AND [Value] NOT LIKE '1900%'
 ORDER BY [days_ago] DESC;

DROP TABLE #dbcc;

Then find out why it stopped, which is the actual question:

SELECT j.[name]                                                    AS [job_name],
       j.[enabled],
       MAX(msdb.dbo.agent_datetime(h.[run_date], h.[run_time]))    AS [last_run],
       MAX(CASE WHEN h.[run_status] = 1
                THEN msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) END) AS [last_success]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
         ON h.[job_id] = j.[job_id] AND h.[step_id] = 0
 WHERE j.[name] LIKE '%Integrity%' OR j.[name] LIKE '%CHECKDB%' OR j.[name] LIKE '%DBCC%'
 GROUP BY j.[name], j.[enabled];

A job that is enabled, running, and whose last success is older than its last run is being killed or failing partway.

How to fix it

Run it now, so you know where you stand:

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Then fix the schedule, according to which cause it was:

• The job is failing: read the job history for the error. A CHECKDB that fails to complete is not the same as a CHECKDB that found nothing, and the failure itself may be corruption.
• The window is too small: this is the most common and the most solvable.
  • Use WITH PHYSICAL_ONLY nightly and a full check weekly. Physical only is substantially faster and catches the majority of real corruption.
  • Split large databases across different nights rather than checking everything every night.
  • Run CHECKDB against a restored copy on another server. The best answer available: it costs production nothing and it tests the backups at the same time. A clean CHECKDB on a restored copy proves both.
• The job was disabled: re-enable it, and add an operator so the next lapse is reported.

Replace the maintenance plan task with a script if that is what you are using. Ola Hallengren’s DatabaseIntegrityCheck handles databases one at a time, continues past a failure, and takes a time limit, none of which the built in task does. That is the difference between one large database stopping the check and one large database being skipped.

How long it takes

About an hour to get the schedule working again. The catch-up run takes as long as the databases are large.

Related reports

Related checks

Frequently asked questions

Is 30 days really too long? It is the threshold this check uses, and it is generous. Weekly is the common recommendation. The number that matters is whether your backup retention is longer than the gap.

The job runs every night and this still fires. Then it is not completing. A killed or failing CHECKDB records nothing, so the date does not move while the job appears to run.

Can I check a restored copy instead? Yes, and it is the better arrangement. Note that the known good date on the production database will not move, so this check keeps firing; track it on the copy instead.

Does PHYSICAL_ONLY update the date? It records a successful run, and it has not performed the logical checks. Use it as the frequent check with a full check on a slower cycle.