Part of the DBA-Tools Project.
Get SQL Agent Job Overview answers “what’s the current state of every job.” This script answers a narrower, more urgent question: “what actually failed recently, and why.” Job history in msdb holds the error message for every failed step, but it’s not somewhere anyone looks unless they already suspect a problem, which means a job can fail quietly for days before anyone notices.
This script pulls every SQL Agent job failure from the last 7 days, with the actual error message from the job step, so there’s no need to dig through SSMS job history one job at a time.
Why SQL Agent Job Failure Summary Matters
A failed backup job, a failed maintenance job, or a failed data pipeline job is exactly the kind of thing that should never go unnoticed, but without alerting configured, nothing surfaces it automatically:
- Surfaces every job failure in the window, not just the ones you happened to check
- Includes the actual error message, so root cause investigation starts immediately instead of requiring a second trip into SSMS
- A pattern of repeated failures on the same job/step is easy to spot once they’re all in one place
- Complements Get SQL Agent Job Overview‘s single “last run outcome” column with full history and error detail
When to Run This Script
- Routine SQL Server health checks
- Investigating a suspected backup or maintenance gap
- After being alerted to a single failure, to check whether it’s isolated or part of a pattern
- Reviewing a server you’ve just inherited, to see its recent operational history
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-SqlAgentJobFailureSummary
Category : configuration-and-environment
Purpose : Show SQL Agent job failures from the last 7 days with readable timestamps and error messages.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-sql-agent-job-failure-summary/)
Requires : db_datareader on msdb
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
j.name AS job_name,
h.step_id,
h.step_name,
msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_datetime,
CAST(h.run_duration / 10000 AS VARCHAR(4)) + 'h '
+ RIGHT('0' + CAST(h.run_duration / 100 % 100 AS VARCHAR(2)), 2) + 'm '
+ RIGHT('0' + CAST(h.run_duration % 100 AS VARCHAR(2)), 2) + 's' AS run_duration,
h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
WHERE h.run_status = 0
AND h.run_date >= CONVERT(INT, CONVERT(CHAR(8), DATEADD(DAY, -7, GETDATE()), 112))
ORDER BY h.instance_id DESC;
The script queries msdb.dbo.sysjobhistory for failed steps (run_status = 0) in the last 7 days, joined to the job name, with the raw run duration converted to a readable h/m/s format and the actual failure message included.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# List every SQL Agent job failure from the last 7 days:
.\run.ps1 Get-SqlAgentJobFailureSummary
# To run against a remote sql server:
.\run.ps1 Get-SqlAgentJobFailureSummary -ServerInstance SQLSERVER01
This script lives in the repo at:
sql/monitoring/jobs/Get-SqlAgentJobFailureSummary.sqlpowershell/wrappers/monitoring/jobs/Get-SqlAgentJobFailureSummary.ps1
Example Output
Zero rows on this lab instance; no job failures in the last 7 days, because SQL Agent itself is currently stopped here (see Get Services Information, which catches exactly this). An empty result is the healthy outcome, but on this box it’s actually masking the real issue: nothing has run to fail. When there are failures, each row looks like this:
| job_name | step_id | step_name | run_datetime | run_duration | message |
|---|---|---|---|---|---|
| DBA Daily Full Backups | 1 | Run Backup | 2026-07-14 02:00:03 | 0h 00m 04s | Executed as user: … Backup device ‘D:\Backups…’ failed to open. Operating system error 3 (failed to retrieve text for this error). |
Understanding the Results
- job_name / step_name — which job and which specific step failed. Multi-step jobs can fail partway through, so the step matters as much as the job.
- run_datetime — when it happened. A cluster of failures at the same time across multiple jobs often points at a shared cause (disk full, network blip, service restart) rather than independent problems.
- run_duration — how long the step ran before failing. A failure after a few seconds usually means a configuration or permissions problem; a failure after a long run often means something ran out of resources partway through.
- message — the actual SQL Agent error text. Usually enough to start troubleshooting without opening SSMS.
Best Practices
- Configure SQL Agent alerts or an external monitoring check for job failures; don’t rely on manually running this script to catch them.
- Investigate same-timestamp failures across multiple jobs as a single incident, not several unrelated ones.
- Review this alongside Get SQL Agent Job Overview to confirm SQL Agent itself is running; a failure summary is only meaningful if the Agent service was up to attempt the jobs in the first place.
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
How far back does job history go?
As far as msdb.dbo.sysjobhistory retains it, controlled by the SQL Agent job history retention settings (row count and per-job caps by default). This script only looks at the last 7 days; adjust the DATEADD filter for a longer window if needed.
Why would a job fail with no error message?
Rare, but it happens when a step is killed externally (server restart mid-job, manual cancellation) rather than failing on its own. The absence of a specific error is itself a clue worth investigating.
Summary
A job failure that nobody notices is functionally the same as not having that job at all, whether it’s a backup, a maintenance task, or a data pipeline. This script exists to close that visibility gap without waiting for someone to configure proper alerting first.
Run this script as part of routine health checks, and treat any result here as worth investigating immediately rather than filing away for later.
Leave a Reply