Find failed SQL Server Agent jobs on all your servers
See which SQL Server Agent jobs failed last night on all your servers, with the failing step and its message, in SQLTreeo Job Timeline. Or use a T-SQL query.
You can see which SQL Server Agent jobs failed on all your servers in one list with SQLTreeo Job Timeline. Its Problems view names the failing step and its message, for last night, the last 24 hours, 7 or 30 days.
That is the quick answer when somebody asks whether last night’s jobs ran, or when an application is missing data and you suspect a job. This article gives the steps, and further down the T-SQL query for one server and the way to run it on many.
See failed jobs in the Problems view
SQLTreeo Job Timeline reads the jobs and the history of all the servers you add, with only SELECT statements and nothing installed on the servers. The Problems view answers this question for all of them at once:
- It lists the failed, canceled and retried runs of last night (18:00 to 08:00), the last 24 hours, 7 days or 30 days.
- Each row names the failing step and the first line of its message, and counts how often that job had a problem in the period. A job that fails every night stands out from one that failed once.
- Select a row to see the message of the run and of its steps that did not succeed. A run that only the archive still holds shows the failing step, with shortened messages. Copy puts it on the clipboard for a ticket.
- Include long runs adds the successful runs that took far longer than the job’s own norm. See Find SQL Server Agent jobs that run longer than usual.
- Double-click a job to open the History view on that job, and Same job on all servers widens that to the jobs with that name in their name, on every server.
The finished runs it reads are merged into an archive on your computer, with the failing step and its message for a run that did not succeed. The views can therefore look back further than what Agent still keeps, for the period set under Settings → Reading the server (90 days by default). The archive only holds what the application has read while it was open: it cannot recover runs that Agent had already removed. See Job history, baselines and long runs.
What it leaves to SQL Server Agent
Job Timeline is a tool for looking, not a monitoring service. It sends no alerts, and it reads while it is open on your computer. For a message when a job fails, use the notification settings of the job itself in SQL Server Agent, with an operator and Database Mail.
With T-SQL: how the history is stored
SQL Server Agent keeps its history in msdb.dbo.sysjobhistory. A run of a job has one row per step that ran, and one row for the job as a whole, which has step_id = 0. That last row carries the outcome of the run in run_status:
| run_status | Meaning |
|---|---|
| 0 | Failed |
| 1 | Succeeded |
| 2 | Retry |
| 3 | Canceled |
| 4 | In progress |
The date and time are stored as integers (run_date as yyyymmdd, run_time as hhmmss). The function msdb.dbo.agent_datetime turns them into a datetime.
Failed jobs of the last 24 hours, with the failing step
The query below lists every run of the last 24 hours that failed, with the step that failed and the start of its message. The failed step is the latest failed step row of the same run: after the outcome row of the previous run and before the outcome row of this one.
DECLARE @since datetime = DATEADD(HOUR, -24, GETDATE());
SELECT j.name AS job_name,
msdb.dbo.agent_datetime(o.run_date, o.run_time) AS started,
f.step_id,
f.step_name,
LEFT(f.message, 200) AS message
FROM msdb.dbo.sysjobhistory AS o
JOIN msdb.dbo.sysjobs AS j ON j.job_id = o.job_id
OUTER APPLY (SELECT TOP (1) h.step_id, h.step_name, h.message
FROM msdb.dbo.sysjobhistory AS h
WHERE h.job_id = o.job_id
AND h.step_id > 0
AND h.run_status = 0
AND h.instance_id < o.instance_id
AND h.instance_id > ISNULL((SELECT MAX(p.instance_id)
FROM msdb.dbo.sysjobhistory AS p
WHERE p.job_id = o.job_id
AND p.step_id = 0
AND p.instance_id < o.instance_id), 0)
ORDER BY h.instance_id DESC) AS f
WHERE o.step_id = 0
AND o.run_status = 0
AND msdb.dbo.agent_datetime(o.run_date, o.run_time) >= @since
ORDER BY started DESC;
To include canceled runs and runs that were retried, change o.run_status = 0 to o.run_status IN (0, 2, 3), and the same in the subquery for the failing step. A job that failed to start at all has no failed step, so its step_id and message are empty. The message of the outcome row itself, step_id = 0, then tells why.
Two limits to keep in mind. The history is only as long as the Agent keeps it: by default 1,000 rows in total and 100 rows per job, so on a server with busy jobs the failures of last night can already be gone. Keep a longer job history explains how to raise it. And a job that never started leaves no row at all, so this query does not find a job that did not run.
Many servers with plain T-SQL
SSMS can run one query on a group of servers. Register the servers in a Central Management Server, right-click the group and choose New Query: the query runs on every server in the group and the results are combined, with the server name as an extra column. For a few servers that works well. It needs a registered group, a login on every server that can read msdb, and every server reachable at the same moment.
Next steps
- Download SQLTreeo Job Timeline. The trial covers every feature and any number of servers, and the tool stays free for up to three servers afterwards.
- Getting started adds the first server and shows the views.
- Permissions and what is read lists what is read from a server, with a script for a read-only login.