Maintenance Plans

Overview

A maintenance plan is three things wearing one name:

Thing Where it lives
The plan A row in a view.
Each subplan An Agent job.
What actually runs An SSIS package stored in msdb.

When a plan fails, the job history says “the step failed, see the package output”, the package output is in sysmaintplan_logdetail, and nothing joins the two for you.

This page joins them: plan, subplan, the job that runs it, whether that job is still there, and the error text from the log detail rather than the job’s shrug.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Maintenance Plans.

Shown only on an instance that can have SQL Server Agent.

Three findings that come out of the shape rather than any single column

A subplan whose job has been deleted

The plan still lists it. The maintenance plan designer still draws it. Nothing runs it, ever again.

A plan that has never logged anything

Either it has never run, or somebody turned its logging off. Those are different problems with the same empty page, and the grid separates them by whether the job behind it has run.

Plans and script based jobs doing the same work

An instance with a backup plan and an Ola Hallengren job has two backup regimes, and the one somebody forgot about is the one that fills the disk.

Reading the grid

Column What it is
Plan The maintenance plan.
Subplan The subplan inside it.
Job The Agent job that runs the subplan, or gone when the job has been deleted.
Runs Executions recorded in the plan log.
Failures How many of those failed.
Last run The most recent one.
What it said The error text from sysmaintplan_logdetail, in full.
Verdict Whether this subplan still runs, whether it logs, and what it last said.

The toolbar

Control What it does
Package store Opens Package Store, where these plans are stored as packages.
Step failures Opens Job Step Failures, the job side of the same failure.
Space and retention Opens msdb Space and Retention.

The log detail is also the table nothing trims

sp_maintplan_delete_log is the only thing that removes a row from sysmaintplan_logdetail. msdb Space and Retention is where that shows up, usually as one of the larger tables on an instance that has run plans for years with logging on.

Where the data comes from

Source What it gives
dbo.sysmaintplan_plans The plans and their descriptions.
dbo.sysmaintplan_subplans The subplans, and the job_id of the job that runs each.
dbo.sysmaintplan_log One row per plan execution.
dbo.sysmaintplan_logdetail The task level detail, which is where the error text is.
dbo.sysjobs Whether the job still exists, and its name.
dbo.sysssispackages The package a plan is stored as, with type 6.

Messages you may see

No maintenance plans are defined here. Which on an instance running Agent jobs is often the right answer, and says nothing about whether maintenance is happening. Job Commands covers the script based kind.

Report Why you would go there
Job Step Failures The Agent side of a plan that fails.
Package Store The packages behind the plans, including the ones with no plan left.
Job Commands The script based maintenance that runs alongside the plans.
msdb Space and Retention What the plan log weighs.
Backup Ledger Whether both regimes are writing backup history.

Frequently asked questions

The job history says the step failed and nothing else. That is the expected behavior, and it is why this page exists. The real message is in the plan log and is in the What it said column.

A subplan has no job. It will never run again. The plan still looks complete in the designer, which is what makes this one worth finding before somebody relies on it.

Should I replace plans with scripts? That is not a decision this page makes. What it will tell you is whether you are currently running both.

The plan log is huge. sp_maintplan_delete_log is the only thing that trims it, and msdb Space and Retention names it in the Trimmed by column.