How to Monitor SQL Server Agent Job Failures (Alerts, Queries and Best Practices)

October 12, 2026

SQL Server Agent runs the jobs that keep a database healthy: backups, index and statistics maintenance, ETL loads and cleanup. When a job fails silently, nobody notices until something breaks, often a restore that turns out to have no recent backup. This guide shows how to get notified about SQL Server Agent job failures, how to query job history, and how to catch jobs that hang instead of failing.

SQL Server Agent job failure monitoring with Database Mail alerts

Step 1: set up Database Mail

Agent notifications need Database Mail. Configure a profile in SSMS (Management, Database Mail) or with T-SQL, then send a test email:

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'DBA Alerts',
     @recipients   = 'dba-team@example.com',
     @subject      = 'Database Mail test',
     @body         = 'If you can read this, Database Mail works.';

Then, in SQL Server Agent properties, open Alert System, tick Enable mail profile, choose the profile and restart the Agent service. Forgetting the restart is the most common reason notifications never arrive.

Step 2: create an operator

EXEC msdb.dbo.sp_add_operator
     @name = N'DBA Team',
     @email_address = N'dba-team@example.com';

Use a shared team mailbox or distribution list, not one person's address, so alerts are not lost when someone is on leave.

Step 3: notify on failure for every job

-- Find jobs with no failure notification
SELECT j.name
FROM msdb.dbo.sysjobs AS j
WHERE j.enabled = 1 AND j.notify_level_email = 0;

-- Add notification to a job (2 = on failure)
EXEC msdb.dbo.sp_update_job
     @job_name = N'Nightly Full Backup',
     @notify_level_email = 2,
     @notify_email_operator_name = N'DBA Team';

Make this part of your job deployment standard, so new jobs never go live without it.

Step 4: review failures with a query

SELECT 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 AS h
JOIN msdb.dbo.sysjobs       AS j ON j.job_id = h.job_id
WHERE h.run_status = 0          -- 0 = failed
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(day, -1, GETDATE())
ORDER BY run_at DESC;

Job history is trimmed by default (1,000 rows in total, 100 per job), so busy servers may lose history quickly. Increase the limits in Agent properties, under History, or keep your own copy in a DBA database.

Step 5: catch long-running and stuck jobs

A job that hangs never fails, so it never sends a failure email. Check for jobs still running past a threshold:

SELECT j.name,
       ja.start_execution_date,
       DATEDIFF(minute, ja.start_execution_date, GETDATE()) AS minutes_running
FROM msdb.dbo.sysjobactivity AS ja
JOIN msdb.dbo.sysjobs        AS j ON j.job_id = ja.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
  AND DATEDIFF(minute, ja.start_execution_date, GETDATE()) > 120;

Wrap this in a small Agent job of its own that emails the team when rows are returned.

Step 6: better error details

Step output: in each job step's Advanced page, log output to a table or a file. The history message is truncated, but the full output often explains the failure.
Error alerts: create Agent alerts for severities 17 to 25 and for errors 823, 824 and 825 (I/O and corruption warnings), all sent to the same operator.
Retry attempts: for steps that hit transient issues, such as a network share being briefly unavailable, set a retry count and interval on the step.

Don't forget the Agent itself

If the SQL Server Agent service is stopped, no jobs run and no failure emails are sent. Set the service to start automatically, and monitor it from outside SQL Server, for example with your server monitoring tool or Windows service alerts.

Azure notes

Azure SQL Managed Instance and SQL Server on Azure VMs include SQL Server Agent. Azure SQL Database does not; use Elastic Jobs, Azure Automation or Azure Data Factory for scheduling there, and Azure Monitor alerts for failure notifications.

Final thoughts

Reliable job monitoring takes an hour to set up: Database Mail, one team operator, failure notifications on every job, a daily failure query, a check for long-running jobs and severity alerts. That hour can save you from discovering a week of failed backups at the worst possible moment.

Related reading:

Search This Blog

SQL & Cloud Lab