Default Maintenance Plan Check Integrity Task

What this check looks for

The maintenance plan package definitions on this instance are searched for the Check Database Integrity task, which appears in the package XML as Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceCheckIntegrityTask.

The check is skipped on Amazon RDS.

Why it matters

Start with the good news, because it matters more than the rest of this page: this instance runs DBCC CHECKDB. A great many do not. This is a low severity finding precisely because the alternative most instances offer is nothing at all.

The problems with the task are real but they are refinements:

  • It runs as all or nothing. One task, all the selected databases, in one step. If it fails on the third database of twelve, the remaining nine are never checked and the job reports a failure with no indication of how far it got. The databases most likely to fail are the large ones, and they are also the ones whose neighbours in the list then get skipped.
  • There is no time limit. On an instance with several large databases the task can run well past the maintenance window and into the working day, where the I/O and tempdb load of a CHECKDB are very noticeable.
  • It cannot skip what it has already verified. Every run checks everything, so a database that was verified clean an hour ago is checked again while one that has not been checked in a month waits its turn behind it.
  • The output goes into the job history and the error log, and nothing summarizes it. A clean run and a run that found corruption look similar until somebody reads the detail, which is the reason for the separate check on CHECKDB errors in the error log.
  • PHYSICAL_ONLY is not offered. On a very large database, a physical only check is substantially faster and catches the majority of real corruption, which makes a nightly physical check plus a weekly full check a practical schedule. The task cannot express that.

How to confirm it yourself

SELECT p.[name]        AS [plan_name],
       sp.[subplan_name],
       j.[name]        AS [job_name],
       j.[enabled]
  FROM msdb.dbo.sysmaintplan_plans AS p WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp WITH (NOLOCK)
         ON sp.[plan_id] = p.[id]
  LEFT JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK)
         ON j.[job_id] = sp.[job_id]
 ORDER BY p.[name];

The question that actually matters is whether every database is genuinely being verified. Run this in each database:

DBCC DBINFO ('YourDatabase') WITH TABLERESULTS;

and read the dbi_dbccLastKnownGood row. A date far in the past, or the zero date, means that database has not passed a CHECKDB, whatever the plan claims to cover.

How to fix it

Replace the task with a script that handles databases individually, so one failure does not stop the rest. Ola Hallengren’s DatabaseIntegrityCheck is the usual choice: it loops database by database, continues past a failure, can be given a time limit, and supports PHYSICAL_ONLY.

A workable schedule for an instance with large databases:

  • Nightly: PHYSICAL_ONLY across everything, which is fast.
  • Weekly: a full DBCC CHECKDB including the logical checks.
  • Always: WITH NO_INFOMSGS, ALL_ERRORMSGS so the output is readable.

Then make sure somebody would know if it failed. A CHECKDB that finds corruption and writes it to a job history nobody reads is only marginally better than not running it. The error log check in this report covers that, and an Agent operator on the job covers the job failing outright.

If you keep the task, at least split it into one subplan per database so a failure stops one database rather than all of them.

How long it takes

About two hours to install and schedule a replacement, and to confirm from dbi_dbccLastKnownGood that every database is now genuinely covered.

Report Why you would go there
Last DBCC CheckDB Known Good by Database The real answer to which databases are verified.
Maintenance Plans Every plan on the instance and what each contains.
Suspect Pages Corruption already recorded, whatever the plan says.
Job History Whether the task completes or fails partway.
Maintenance Window Finder Whether there is room to run this properly.
Alerts and Operators Whether a failure would reach anybody.
Check
DBCC CheckDB never run Databases this task is not covering at all.
DBCC CheckDB not run recently Coverage that has quietly lapsed.
DBCC CHECKDB Corruption Errors Found What to do when it finds something.
Default Maintenance Plan Reindex Task The task that usually shares the window with this one.
Jobs without failure notification Why nobody heard about the failed run.

Frequently asked questions

Is the task actually wrong? No, and that is why this is Low. It runs a real DBCC CHECKDB. The objection is to how it handles failure and scale, not to what it does.

We only have one database. Then the all or nothing objection does not apply to you, and the remaining ones are about runtime and reporting. This is a low priority in that case.

Is PHYSICAL_ONLY good enough? For a nightly check on a very large database, yes, as long as a full check runs on a schedule too. It skips the logical consistency checks, which are the slow part and catch a smaller share of real-world corruption.

How do I know it found something? Read the error log, or rely on the corruption check in this report, which reads it for you. The job succeeding does not mean the databases were clean in every configuration.