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

By Dan Ochoa · Published 2026-10-25 · Updated 2026-10-25
Canonical: https://cronradar.com/blog/sql-server-agent-job-monitoring

> Query msdb for SQL Server Agent job history, failures, and running jobs, set up failure email, and catch what Agent misses: runs that never start or that hang.

**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](https://cronradar.com/blog/pg-cron-monitoring).

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

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

| Platform                                                    | Agent               | What differs                                                                                                                                                                                                                                                                                                                                                                                                        |
| ----------------------------------------------------------- | ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SQL Server Enterprise, Standard, Developer (Web until 2022) | Yes                 | Everything here applies.                                                                                                                                                                                                                                                                                                                                                                                            |
| SQL Server Express                                          | No                  | The [2022](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022) and [2025](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2025) feature tables list Agent and Database Mail as unavailable. Run `sqlcmd` from Task Scheduler instead, monitored as in [Windows Task Scheduler monitoring](https://cronradar.com/blog/windows-task-scheduler-monitoring). |
| SQL Server on Linux                                         | Disabled by default | [Enable it with mssql-conf](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent). CmdExec and PowerShell steps and alerts [aren't supported](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2022#unsupported-features-and-services).                                                                                                                 |
| Azure SQL Managed Instance                                  | Always running      | Settings are read-only. Alerts, proxies, the event log, and scripts on disk [aren't supported](https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance#sql-agent-job-limitations-in-sql-managed-instance).                                                                                                                                                               |
| Azure SQL Database                                          | No                  | Microsoft recommends [elastic jobs](https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview).                                                                                                                                                                                                                                                                                            |

On Linux:

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

## Query msdb for Job Status and History

| Table                             | What it holds                                                                                                                                                                          |
| --------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `sysjobs`                         | One row per job, including `enabled`                                                                                                                                                   |
| `sysjobsteps`                     | Each step's command, retries, and on-success and on-failure actions                                                                                                                    |
| `sysjobschedules`, `sysschedules` | Schedules and next run time; `sysjobschedules` [refreshes every 20 minutes](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobschedules-transact-sql) |
| `sysjobhistory`                   | One row per finished step, plus a `step_id` 0 row for the job's outcome                                                                                                                |
| `sysjobactivity`, `syssessions`   | Job state per Agent session; Agent [starts a new session each time it starts](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-syssessions-transact-sql)   |

In [sysjobhistory](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql), `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](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-jobhistory-transact-sql)).

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

```sql
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

```sql
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:

```sql
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](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-job-transact-sql) nor [sp_add_jobstep](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql) 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

```sql
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](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-server-services-transact-sql)), 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](https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance#job-history)), and [removes the oldest rows](https://learn.microsoft.com/en-us/ssms/agent/sql-server-agent-properties-history-page) 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](https://learn.microsoft.com/en-us/ssms/agent/resize-the-job-history-log)).
- **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](https://support.microsoft.com/servicing/sql/sql-server-2022/cumulative-update/kb5024352-error-set-sql-server-agent-job-history-log)).
- **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](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-purge-jobhistory-transact-sql) daily:

```sql
-- 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](https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail)).
2. An operator ([sp_add_operator](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-operator-transact-sql)).
3. The job's notification level ([sp_update_job](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql)).
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](https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail)). On Linux, set `sqlagent.databasemailprofile` with [mssql-conf](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf). On Managed Instance, the profile must be named `AzureManagedInstance_dbmail_profile`.

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

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](https://learn.microsoft.com/en-us/ssms/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:

| Situation                                                 | Agent email                      |
| --------------------------------------------------------- | -------------------------------- |
| The job's outcome is Failed                               | Yes, with the setup above        |
| A step failed, but its on-failure action let the job pass | No                               |
| The Agent service is stopped                              | No: Agent is what sends the mail |
| The job or its schedule is disabled                       | No: the job doesn't run          |
| The run never starts for another reason                   | No                               |
| The job runs far longer than usual                        | No                               |
| Database Mail can't deliver                               | No: check the Database Mail log  |

## 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](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities), 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](https://cronradar.com/blog/how-to-know-if-cron-job-ran). A [dead man's switch](https://cronradar.com/blog/cron-dead-mans-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](https://docs.cronradar.com/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](https://learn.microsoft.com/en-us/ssms/agent/sql-server-agent-fixed-database-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](https://learn.microsoft.com/en-us/windows/win32/procthread/environment-variables). Put the key in a file only the Agent account can read:

```powershell
# 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](https://curl.se/docs/manpage.html#-K): `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](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions)). CmdExec steps run as the Agent service account unless a [proxy](https://learn.microsoft.com/en-us/ssms/agent/create-a-cmdexec-job-step) 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](https://msrc.microsoft.com/update-guide/vulnerability/CVE-2023-38545), and on Windows 10 and 11 [since build 17063](https://curl.se/windows/microsoft.html).

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.

```sql
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](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility). At run time Agent replaces `STRTDT` and `STRTTM` with the job's start date and time ([tokens](https://learn.microsoft.com/en-us/ssms/agent/use-tokens-in-job-steps)), 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](https://learn.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent) 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](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql) example runs "when the time on the server is 01:00". Managed Instance uses the [instance time zone](https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview), UTC unless chosen otherwise at creation. CronRadar evaluates schedules in UTC. Where the server observes daylight saving time, the [schedule docs](https://docs.cronradar.com/schedules) 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:

| Agent schedule                   | Server on UTC  | Server on US Eastern |
| -------------------------------- | -------------- | -------------------- |
| Daily at 01:30                   | `30 1 * * *`   | `30 5 * * *`         |
| Weekdays at 06:00                | `0 6 * * 1-5`  | `0 10 * * 1-5`       |
| Weekly on Sunday at 03:00        | `0 3 * * 0`    | `0 7 * * 0`          |
| Every 15 minutes, starting 00:05 | `5/15 * * * *` | `5/15 * * * *`       |
| Monthly, first Monday at 09:00   | `0 9 1-7 * 1`  | `0 13 1-7 * 1`       |

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.