SQL Server Agent Job Monitoring: History, Failures, and Missed Runs

To monitor SQL Server Agent jobs, query the job history in msdb, have Agent email an operator through Database Mail when a job fails, and add an external check that notices when a job doesn't run at all. Agent records every job and step that finishes in msdb.dbo.sysjobhistory and can mail you when a job fails. It says nothing about a run that never started or a job stuck for six hours, and nothing at all while the Agent service is stopped.

This guide covers SQL Server 2019, 2022, and 2025 and Azure SQL Managed Instance. Every query only reads msdb. For PostgreSQL's in-database scheduler, see monitoring pg_cron jobs.

Where SQL Server Agent Runs, and Where It Doesn't#

Platform support, from Microsoft Learn (checked October 24, 2026):

On Linux:

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server

Query msdb for Job Status and History#

In sysjobhistory, run_status is 0 Failed, 1 Succeeded, 2 Retry, 3 Canceled, or 4 In Progress. run_date and run_time are yyyyMMdd and HHmmss integers in server local time, so compare them with GETDATE(). run_duration is an HHmmss integer: 1 hour, 2 minutes, 3 seconds is 10203. The step_id 0 row is the job's outcome (sp_help_jobhistory).

The queries convert dates with DATETIMEFROMPARTS, like Microsoft's sysjobhistory example; msdb.dbo.agent_datetime, common in older scripts, has no Microsoft Learn reference page. Run them as a sysadmin, the only role that can read syssessions.

Last Outcome of Every Job#

SELECT j.name AS job_name,
       j.enabled,
       h.run_start,
       CASE h.run_status
           WHEN 0 THEN 'Failed'
           WHEN 1 THEN 'Succeeded'
           WHEN 2 THEN 'Retry'
           WHEN 3 THEN 'Canceled'
           WHEN 4 THEN 'In progress'
           ELSE 'No history'
       END AS last_outcome,
       h.duration_seconds,
       h.message
FROM msdb.dbo.sysjobs AS j
OUTER APPLY (
    SELECT TOP (1)
        DATETIMEFROMPARTS(run_date / 10000, run_date / 100 % 100, run_date % 100,
                          run_time / 10000, run_time / 100 % 100, run_time % 100, 0) AS run_start,
        run_duration / 10000 * 3600 + run_duration / 100 % 100 * 60 + run_duration % 100 AS duration_seconds,
        run_status,
        message
    FROM msdb.dbo.sysjobhistory
    WHERE job_id = j.job_id
      AND step_id = 0
    ORDER BY instance_id DESC
) AS h
ORDER BY j.name;

No history means the job never finished a run, or its rows rolled off.

Failed Runs and Steps in the Last 24 Hours#

SELECT j.name AS job_name,
       h.step_id,
       h.step_name,
       t.run_start,
       h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
CROSS APPLY (
    SELECT DATETIMEFROMPARTS(h.run_date / 10000, h.run_date / 100 % 100, h.run_date % 100,
                             h.run_time / 10000, h.run_time / 100 % 100, h.run_time % 100, 0)
) AS t (run_start)
WHERE h.run_status = 0
  AND t.run_start >= DATEADD(HOUR, -24, GETDATE())
ORDER BY t.run_start DESC;

Keep the step rows: a step can fail while its job reports success (see step settings below). Add AND h.step_id = 0 for failed job outcomes only.

Jobs Running Longer Than N Minutes#

A job that was running when Agent stopped keeps a start time and no stop time in that old session, so filter to the newest session:

DECLARE @max_minutes int = 120;

SELECT j.name AS job_name,
       a.start_execution_date,
       DATEDIFF(MINUTE, a.start_execution_date, GETDATE()) AS minutes_running
FROM msdb.dbo.sysjobactivity AS a
JOIN msdb.dbo.sysjobs AS j ON j.job_id = a.job_id
WHERE a.session_id = (SELECT TOP (1) session_id
                      FROM msdb.dbo.syssessions
                      ORDER BY agent_start_date DESC)
  AND a.start_execution_date IS NOT NULL
  AND a.stop_execution_date IS NULL
  AND a.start_execution_date < DATEADD(MINUTE, -@max_minutes, GETDATE());

Drop the last condition to list every running job. Neither sp_add_job nor sp_add_jobstep has a timeout parameter, so this query is how you find a hung job. To be alerted, run it in a watchdog job as IF EXISTS (...) THROW 50001, N'A job has run longer than 120 minutes.', 1; and let that job's failure email fire.

Enabled Jobs That Won't Run#

SELECT j.name AS job_name,
       CASE
           WHEN s.enabled_schedules = 0 THEN 'No enabled schedule'
           WHEN s.next_run IS NULL THEN 'No upcoming run'
           ELSE 'Next run is in the past'
       END AS problem,
       s.next_run
FROM msdb.dbo.sysjobs AS j
CROSS APPLY (
    SELECT COUNT(*) AS enabled_schedules,
           MIN(CASE WHEN js.next_run_date > 0 THEN
               DATETIMEFROMPARTS(js.next_run_date / 10000, js.next_run_date / 100 % 100,
                                 js.next_run_date % 100, js.next_run_time / 10000,
                                 js.next_run_time / 100 % 100, js.next_run_time % 100, 0)
           END) AS next_run
    FROM msdb.dbo.sysjobschedules AS js
    JOIN msdb.dbo.sysschedules AS sc ON sc.schedule_id = js.schedule_id
    WHERE js.job_id = j.job_id
      AND sc.enabled = 1
) AS s
WHERE j.enabled = 1
  AND (s.enabled_schedules = 0
       OR s.next_run IS NULL
       OR s.next_run < DATEADD(MINUTE, -30, GETDATE()));

The 30 minutes of slack cover the 20-minute refresh. On-demand jobs show as No enabled schedule; exclude them by name. A next run in the past points at the Agent service: check it with SELECT servicename, status_desc FROM sys.dm_server_services; (reference), from outside Agent, since an Agent job stops when Agent does.

Agent History Rolls Off Faster Than You Expect#

By default Agent keeps 1,000 history rows in total and 100 per job (stated in Microsoft's Managed Instance guide), and removes the oldest rows as new ones arrive. Each run writes a row per step plus the outcome row, so a four-step job every five minutes fills its 100 rows in under two hours, and on a busy instance the total limit pushes out a weekly job's older runs.

To change the limits:

• Windows: in SSMS, SQL Server Agent > Properties > History, set Maximum job history log size and Maximum job history rows per job (steps).
• Linux: SSMS can't set them. On SQL Server 2022 CU 2 or 2019 CU 20 and later, run sudo /opt/mssql/bin/mssql-conf set sqlagent.jobhistorymaxrows 50000 and set sqlagent.jobhistorymaxrowsperjob the same way (KB5024352).
• Managed Instance: fixed at 1,000 and 100.

Remove agent history on the same page is a one-time purge. For time-based retention, raise the limits and run sp_purge_jobhistory daily:

-- Keep 30 days of Agent history.
DECLARE @cutoff datetime = DATEADD(DAY, -30, GETDATE());
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @cutoff;

Built-In Failure Email and What It Misses#

Database Mail, an Operator, and the Job#

Agent's failure email needs all four of these:

1. Database Mail with an account and profile (configure).
2. An operator (sp_add_operator).
3. The job's notification level (sp_update_job).
4. A mail profile for Agent: SQL Server Agent > Properties > Alert System, Enable mail profile, choose Database Mail and the profile, then restart Agent (steps). On Linux, set sqlagent.databasemailprofile with mssql-conf. On Managed Instance, the profile must be named AzureManagedInstance_dbmail_profile.

USE msdb;
GO
EXEC dbo.sp_add_operator
    @name = N'DBA Team',
    @email_address = N'[email protected]';

EXEC dbo.sp_update_job
    @job_name = N'Nightly backup',
    @notify_level_email = 2, -- 0 never, 1 on success, 2 on failure, 3 always
    @notify_email_operator_name = N'DBA Team';
GO

sp_add_job also defaults @notify_level_eventlog to 2, writing job failures to the Windows application log for event-log collectors. Agent alerts are separate: they fire on SQL Server errors, performance conditions, or WMI events, not job schedules.

What Agent Never Tells You#

Agent sends job email "upon the completion of this job" (sp_update_job). Anything else stays silent:

Step Settings That Hide Failures#

• Retries. @retry_attempts and @retry_interval (minutes) retry a failed step before its on-failure action runs, so three retries 30 minutes apart delay the email by at least 90 minutes. A step that succeeds on a retry sends nothing; only run_status 2 rows show it.
• On-failure actions. The default quits reporting failure. Quit the job reporting success (1) or Go to the next step (3) turns a failed step into a successful job with no failure email. Find them with SELECT job_id, step_id, step_name FROM msdb.dbo.sysjobsteps WHERE on_fail_action IN (1, 3);. The reporting steps below appear there on purpose.
• T-SQL steps. Severity 0 through 10 messages are informational, so RAISERROR(N'...', 10, 1) or PRINT reports a problem without failing the step. A CATCH block that logs and doesn't rethrow lets the step succeed; end it with THROW;.
• CmdExec steps. Success is one exit code, Process exit code of a successful command (default 0). A batch file whose last command succeeds can exit 0 after an earlier failure; return the failing code with exit /b.

Report Each Run to an External Monitor#

History only exists for runs that happened, the same limit covered in how to know if a cron job ran. A dead man's switch covers the rest: the job reports each run to a service that expects reports on a schedule. CronRadar has no SDK or auto-discovery for SQL Server Agent, so each job reports with its own HTTP check-ins:

1. Step 1 reports start, then continues to the work whether or not the report got through.
2. The work steps send any failure to the fail step.
3. A step reports complete and quits reporting success.
4. A step reports fail and quits reporting failure, so Agent's outcome, history, and email are unchanged.

Every check-in carries schedule, so the first run creates the monitor and the next run recreates a deleted one. Each times out after five seconds and can't change the job's result. A fail alerts immediately; a missed run alerts after the grace period (API reference).

This needs outbound HTTPS from the SQL Server host to cron.life, which many locked-down database servers don't allow. Rather than opening the server to the internet, run the work from a separate job runner that can reach it, such as a scheduled task or cron job that calls sqlcmd and reports around it. Do the same on Linux (no CmdExec or PowerShell steps) and Managed Instance (no proxies or scripts on disk).

Keep the API Key Out of msdb#

Step commands live in msdb.dbo.sysjobsteps. SQLAgentReaderRole and SQLAgentOperatorRole members can view the properties of every local job (roles), and sp_help_jobstep returns the command, so a key pasted into a step is readable by anyone with Agent read access. A system environment variable is no better: system variables are set for everyone. Put the key in a file only the Agent account can read:

# Run once in an elevated PowerShell session on the SQL Server host.
$key = Read-Host -Prompt 'CronRadar API key'
New-Item -ItemType Directory -Path 'C:\ProgramData\CronRadar' -Force | Out-Null
Set-Content -Path 'C:\ProgramData\CronRadar\curl-auth.txt' -Value ('user = "' + $key + ':"') -Encoding Ascii
icacls 'C:\ProgramData\CronRadar\curl-auth.txt' /inheritance:r /grant:r 'NT Service\SQLSERVERAGENT:R' 'BUILTIN\Administrators:F'

It's a curl config file: user sends the key as the Basic auth username with an empty password, off the command line. NT Service\SQLSERVERAGENT is Agent's per-service SID on a default instance; named instances use NT Service\SQLAGENT$<instance_name> (service accounts). CmdExec steps run as the Agent service account unless a proxy is set; if so, grant read to the proxy's Windows account. Use a separate CronRadar application and key per environment.

Build the Job#

curl.exe is in C:\Windows\System32 on Windows Server 2019 and 2022, both listed as affected products in Microsoft's curl CVE-2023-38545 advisory, and on Windows 10 and 11 since build 17063.

The example assumes a server clock on US Eastern time and a backup at 01:30 that usually takes up to 30 minutes: 30 5 * * * in UTC with a 5,400-second grace period, as the schedules section explains.

USE msdb;
GO
DECLARE @job sysname = N'Nightly backup',
        @report nvarchar(1000) =
            N'C:\Windows\System32\curl.exe --silent --show-error --fail --max-time 5 --output NUL '
          + N'--config C:\ProgramData\CronRadar\curl-auth.txt --request POST --get '
          + N'--data-urlencode "schedule=30 5 * * *" --data-urlencode "gracePeriod=5400" '
          + N'--data-urlencode "run_id=nightly-backup-$(ESCAPE_NONE(STRTDT))$(ESCAPE_NONE(STRTTM))" ',
        @url nvarchar(200) = N'https://cron.life/api/v1/ping/nightly-backup';
DECLARE @start nvarchar(1300) = @report + @url + N'/start',
        @complete nvarchar(1300) = @report + @url + N'/complete',
        @fail nvarchar(1400) = @report
            + N'--data-urlencode "message=Agent job Nightly backup failed; see its job history" '
            + @url + N'/fail';

EXEC dbo.sp_add_job @job_name = @job,
    @notify_level_email = 2, @notify_email_operator_name = N'DBA Team';

-- 1: report start, then run the backup whether or not the report got through.
EXEC dbo.sp_add_jobstep @job_name = @job, @step_id = 1, @step_name = N'Report start',
    @subsystem = N'CmdExec', @command = @start,
    @on_success_action = 3, @on_fail_action = 3;

-- 2: the work. After its retry, a failure goes to step 4.
EXEC dbo.sp_add_jobstep @job_name = @job, @step_id = 2, @step_name = N'Back up Sales',
    @subsystem = N'TSQL', @database_name = N'master',
    @command = N'BACKUP DATABASE [Sales] TO DISK = N''D:\Backup\Sales.bak'' WITH CHECKSUM, INIT;',
    @retry_attempts = 1, @retry_interval = 5,
    @on_success_action = 3, @on_fail_action = 4, @on_fail_step_id = 4;

-- 3: report complete. The backup succeeded, so the job succeeds either way.
EXEC dbo.sp_add_jobstep @job_name = @job, @step_id = 3, @step_name = N'Report complete',
    @subsystem = N'CmdExec', @command = @complete,
    @on_success_action = 1, @on_fail_action = 1;

-- 4: report fail, then quit reporting failure so Agent's outcome and email are unchanged.
EXEC dbo.sp_add_jobstep @job_name = @job, @step_id = 4, @step_name = N'Report fail',
    @subsystem = N'CmdExec', @command = @fail,
    @on_success_action = 2, @on_fail_action = 2;

EXEC dbo.sp_add_jobschedule @job_name = @job, @name = N'Daily at 01:30',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 013000;
EXEC dbo.sp_add_jobserver @job_name = @job;
GO

Run the script in SSMS with SQLCMD mode off, or with sqlcmd -x, because sqlcmd otherwise treats $(...) as its own variables. At run time Agent replaces STRTDT and STRTTM with the job's start date and time (tokens), so one run's reports share a run_id. Agent fails steps that use a token without an escape macro; ESCAPE_NONE is safe here because both values are digits.

Windows Server 2016 has no curl.exe. There, make the reporting steps PowerShell steps that read the key from a file with the same ACL and call Invoke-WebRequest -TimeoutSec 5 inside try/catch, so a failed report never fails the step.

Test with EXEC msdb.dbo.sp_start_job @job_name = N'Nightly backup';, read step 1's history message for curl errors, and confirm the run appears in CronRadar.

If the backup hangs, the start arrives and nothing follows. Every check-in moves the expected time to the job's next scheduled run; once that time plus the grace period passes with the last event still a start, the monitor goes critical as stuck. For a nightly job, that's the next night. CronRadar has no per-job maximum runtime, so use the watchdog query for faster hang detection.

Translate Agent Schedules to UTC Cron#

Agent runs schedules on the server's clock; Microsoft's sp_add_schedule example runs "when the time on the server is 01:00". Managed Instance uses the instance time zone, UTC unless chosen otherwise at creation. CronRadar evaluates schedules in UTC. Where the server observes daylight saving time, the schedule docs say to use the earlier of the two UTC times and a grace period of at least an hour plus the usual run time. For US Eastern, that's UTC−4:

Crossing midnight moves the day: Friday at 22:00 Eastern is 0 2 * * 6. First Monday works because CronRadar expects a run only on dates matching both day fields.

Some Agent schedules have no five-field equivalent: intervals in seconds (Agent allows 10), intervals that don't divide the hour (Agent counts from the start time, so every 7 minutes runs at 00:56 then 01:03, while */7 restarts each hour), and last-day-of-month, start-with-Agent, and CPU-idle schedules. Change these to a schedule cron can express, such as every 5 minutes instead of 7.

Frequently Asked Questions#

How do I get notified when a SQL Agent job fails?#

Configure Database Mail, create an operator with sp_add_operator, and set @notify_level_email = 2 with that operator on the job. Then enable a mail profile under SQL Server Agent > Properties > Alert System and restart Agent, or it can't send the email. Agent only emails about runs that finished, so a run that never starts needs an external check.

How do I see SQL Agent job history?#

In SSMS, right-click the job and choose View History. In T-SQL, run EXEC msdb.dbo.sp_help_jobhistory @job_name = N'Nightly backup'; or query msdb.dbo.sysjobhistory. Rows with step_id 0 are job outcomes; the rest are steps.

How do I find SQL Agent jobs that are currently running?#

Query msdb.dbo.sysjobactivity for rows with a start_execution_date and no stop_execution_date, limited to the newest session in msdb.dbo.syssessions. Without the session filter, jobs that were running when Agent last stopped look like they're still running.

Why didn't my SQL Agent job run?#

Check whether the Agent service is running (sys.dm_server_services), whether the job and its schedule are enabled, and whether the schedule has an upcoming run. Express edition has no Agent, and on Linux, Agent stays disabled until you enable it with mssql-conf.

Why is my SQL Agent job history missing?#

Agent keeps 1,000 rows in total and 100 per job by default, deleting the oldest as new ones arrive, so frequent or multi-step jobs push out older runs quickly. Raise both limits in Agent's properties, or with mssql-conf on Linux, and use a scheduled sp_purge_jobhistory for time-based retention. On Managed Instance the limits are fixed, so keep longer records outside msdb.