Job Step Failures

Overview

Failed Jobs answers the job level question and stops there, which is exactly where the useful part begins.

A job with nine steps that fails every night fails at one of them, and the message that matters is on that step’s history row. The job outcome row says only “the job failed” and names the step.

The Job Step Failures page: which step failed, and what it said
The whole page over seven days. Fifty failures across two steps, plus the thirteen steps carrying a failure from before this window on the step itself.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Job Step Failures.

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

Two sources, answering different halves

sysjobhistory, filtered to step rows

What happened inside the window.

Forty identical failures are one finding, so the messages are grouped by what they say rather than listed. The database name, the number and the timestamp are exactly what differs between otherwise identical messages, and those are what the clustering takes out.

sysjobsteps, which carries each step’s own last outcome

Agent keeps last_run_outcome, last_run_duration and last_run_date on the step itself, whether or not the history has been purged.

That is how a step which last failed in March still appears on a page whose window is a week, and it is the only way to find a step that has never once succeeded.

The retry count matters more than it looks

A step set to retry five times at one minute writes six history rows for one failure.

A page that counts rows reports six failures where a DBA would say one. The Failed column is written as failures out of runs for that reason, and Retries is beside it so the arithmetic is visible.

Reading the chart

The bars: steps with a failure, a retry or a cancellation in the window
Two steps account for all fifty failures in the window. Everything below them failed the last time it ran, which Agent keeps on the step whether or not the history is still there.

One bar per step with a failure, a retry or a cancellation inside the window, with the failure count on the right and the job and step named beneath.

The short bars labeled failed last time are the second source: a step whose own last_run_outcome still says failure from before the window. That is how a step which last ran in March appears on a page showing a week.

The bars draw the top thirteen of the fifteen. The grid holds them all.

Reading the grid

The grid: failures out of runs, the retry setting and the grouped message
Failed is written as failures out of runs, with Retries beside it, so a step set to retry five times cannot read as six separate failures.
Column What it is
Job The job.
Step The step that failed.
Subsystem T-SQL, CmdExec, PowerShell, SSIS and the rest.
Failed How many runs failed, out of how many runs there were.
Last failure When it last failed.
Retries The step’s retry setting, and how many retry rows are in the count.
What it said The grouped message, with the parts that vary between occurrences taken out.
Verdict Whether this is a step that always fails, one that fails sometimes, or one that failed once and has been fine since.

The toolbar

Control Options Default
Window 24 hours, 7 days, 30 days 7 days
Failed jobs Opens the Failed Jobs report.
What the steps run Opens Job Commands.
Agent activity Opens Agent Activity.

Where the data comes from

Source What it gives
dbo.sysjobhistory Step level rows: step_id, step_name, message, run_status, run_date, run_time, run_duration, retries_attempted.
dbo.sysjobsteps last_run_outcome, retry_attempts, retry_interval, and the subsystem.
dbo.sysjobs The job names and enabled state.

The Agent time encoding. run_duration of 130 is one minute thirty seconds, not one hundred and thirty seconds. Every duration on this page is converted before it is compared.

Messages you may see

There are no job steps on this instance. Every job has at least one step, so an instance with jobs cannot reach this page empty unless this login cannot see them.

Report Why you would go there
Failed Jobs The job level view, on a timeline.
Job Commands What the failing step actually runs.
Agent Activity Whether Agent was even up when the step was due.
Missed Runs The failure this page cannot see: a step that never ran at all.
Agent Security An “unable to determine if the owner has server access” message lands here.
Maintenance Plans When the failing step is a maintenance plan subplan, the real error is in the plan log.

Frequently asked questions

Failed Jobs shows six failures and this page shows one. Because the six are one failure and five retries. This page groups them; the retry count says how many rows they came from.

A step shows a last failure from months ago and my window is a week. That row came from sysjobsteps rather than from the history. Agent keeps the step’s own last outcome after the history has been trimmed.

Why are the messages grouped? Because a message that names a different database or a different row count each time is the same failure forty times over, and a list of forty rows hides the other three problems underneath them.

The message is truncated. The grid shows what fits. Double-click the row, or use the right-click menu, for the whole message.