Failed SQL Agent Job Triage

October 9, 2026
| No Comments

Step 1: Recent failures

SELECT TOP (50) j.name AS job_name, h.step_id, h.step_name,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_at,
       h.run_duration, h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.run_status = 0
ORDER BY run_at DESC;

run_status 0 = failed. step_id 0 is the overall job outcome; the row for the failed step holds the actual error. History messages are truncated, so if the step writes to an output file (job step properties, Advanced page), the full output is in that file.

Step 2: Common causes

  • Permissions: the step runs as the SQL Agent service account, or a proxy, which cannot reach a share, linked server or database.
  • Job owner disabled: the job is owned by a person’s login that has been disabled or removed. Check owners with the query below.
  • Availability groups: the job ran on a secondary where the database is not writable (see Basic vs Advanced High Availability for the primary-replica check).
  • Disk space: backup and maintenance jobs fail when the target volume is full.
  • Blocking or timeouts: the step was blocked or killed (see Blocking).
SELECT j.name AS job_name, SUSER_SNAME(j.owner_sid) AS job_owner, j.enabled
FROM msdb.dbo.sysjobs j
ORDER BY job_owner, job_name;

Step 3: Jobs running right now

SELECT j.name AS job_name, ja.start_execution_date
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
  AND ja.start_execution_date IS NOT NULL
  AND ja.stop_execution_date IS NULL;

Step 4: Rerun or stop a job (NEEDS APPROVAL for jobs that change data)

EXEC msdb.dbo.sp_start_job @job_name = N'YourJobName';

-- Start from a particular step
EXEC msdb.dbo.sp_start_job @job_name = N'YourJobName', @step_name = N'YourStepName';

-- Stop a running job
EXEC msdb.dbo.sp_stop_job @job_name = N'YourJobName';

From my SQL Server DBA handbook.

Troubleshooting