11.2 Job Alerts, Notifications, and Agent Troubleshooting

Operators and Notification Channels

An operator is a definition of who should be notified when an alert fires or a job completes. Each operator has up to three notification channels:

• Email address: the primary channel; requires Database Mail to be configured and enabled for SQL Server Agent mail.
• Pager email: an alternate email address for pager notifications, with a pager duty schedule so pages only fire on specific days and during a duty window.
• Net send: a legacy Windows network message to a machine name (deprecated in modern Windows; the exam lists it but rarely expects you to choose it).

Operators are created under SQL Server Agent > Operators in SSMS or with sp_add_operator. An operator can be notified on any combination of job completion states (success, failure, completion) and can be the target of one or more alerts. A best practice is to define a team operator (a distribution list) rather than an individual, so paging survives staff changes.

Database Mail for Agent Notifications

For email notifications, Agent relies on Database Mail, not SQL Mail (which is deprecated). The setup is two layers:

1. Configure Database Mail in SQL Server Management Studio (Management > Database Mail), defining a mail profile with an SMTP account and a public profile. Test it with sp_send_dbmail before relying on it.
2. Enable Database Mail for Agent: open SQL Server Agent properties, and on the Alert System page, check Enable mail profile, select the mail system (Database Mail), and pick the profile. Without this step, Agent cannot send mail even if Database Mail itself works.

The fail-safe operator is also defined on this Alert System page. The fail-safe operator receives a notification when an alert fires that has no operator assigned, or when pager or email delivery to the assigned operator fails. The exam frequently tests the fail-safe concept as the safety net for alerts. On Azure SQL Managed Instance, Database Mail is supported and is the standard channel for Agent notifications, configured with an SMTP profile just like on a SQL Server VM.

Alert Types

Agent supports three alert types:

SQL Server event alerts only fire for errors written to the Windows application log - many engine errors are written there by default, but a custom error raised with RAISERROR ... WITH LOG is needed to surface application-defined messages. WMI alerts use a namespace like \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER and a WQL query. Performance condition alerts are the classic answer when a scenario describes reacting to a sustained counter value rather than an error message.

Alert Response

When an alert fires, its Response page lets you do one or both of:

• Execute a job: pick an existing Agent job to run; a common pattern is an alert that fires a job to collect diagnostics or attempt a remediation.
• Notify operators: send the alert to one or more operators via email, pager, or net send.

The alert also has an Include alert error text option that appends the error message body to the notification, which is usually desirable for triage. Alerts are defined at the Agent level (not inside a job) and can be triggered by anything that matches their criteria, including events raised by jobs themselves.

Job History and Retention

Job execution history is stored in msdb in dbo.sysjobhistory (one row per step outcome and per job outcome) and dbo.sysjobservers. The rows carry run date/time as an integer-encoded value, so the standard helper functions msdb.dbo.agent_datetime and dbo.fn_sysjobhistory_row_details are used to make them readable. The Job Activity Monitor in SSMS surfaces current and recent job runs interactively and is the fastest way to see whether a job is running, idle, or failed.

History retention is limited to avoid unbounded growth. On the SQL Server Agent properties > History page you configure two limits:

• Limit size of job history log (rows) - default 1,000 rows total, with a per-job cap of 100 rows.
• **Remove agent history older than N days/weeks/months - an optional automatic purge.

A common trap: with the default retention, a job that runs every minute overwrites its own history before you can investigate a failure. For frequent jobs, either increase the per-job cap or write step output to a file or table for long-term retention. On SQL MI the same controls apply through SSMS and T-SQL.

Troubleshooting Approach

A structured troubleshooting path for a failed Agent job:

1. Is the Agent service running? On a VM, check Services or SQLServerAgent via xp_servicecontrol or the SQL Server Configuration Manager. On SQL MI, the platform keeps it up, but you can still check the job's enabled state.
2. Is the job enabled and its schedule enabled? A disabled job or a disabled schedule will not fire; the Job Activity Monitor shows a disabled state.
3. Inspect the job history for the failing step. Expand the step's row for the error text; on sysjobhistory, step rows have run_status 0 (failed), 1 (succeeded), 3 (cancelled).
4. Check the step's security context. If the step uses a proxy, verify the proxy's credential is valid and that the proxy is allowed for the step's subsystem. If no proxy is set, the step runs as the Agent service account, which may lack permissions.
5. Check the job owner. The job owner must be a login with permission to do the job's work; if the owner is a Windows login that has been removed, the job may fail to start.
6. Review the SQL Server Agent error log. The Agent log (separate from the SQL Server error log) captures start/stop, schedule misses, and subsystem errors.
7. Enable step output logging. On the step's Advanced page, set an output file path or check Log to table to capture the step's full output for replay.

Common Failure Causes

• Proxy credentials: the credential's secret is wrong or the proxy is not mapped to the subsystem. Fix by recreating the credential and re-mapping the proxy.
• Job owner permissions: a job owned by a low-privilege login fails on a step needing elevated rights. Fix by changing ownership to sa or a login in the right role.
• Step security context: a T-SQL step runs in the job owner's database context; if the owner lacks access to the target database, the step fails. Use a proxy for subsystems that need different credentials.
• Disabled Agent: the Agent service is stopped or disabled - a classic "nothing is running" symptom.
• Disabled schedule: the schedule was disabled manually or expired at its end date.
• msdb issues: msdb is full, read-only, or suspected - Agent cannot write history or job state. Check msdb free space and state.
• Failed schedule due to time change: DST transitions or clock drift can shift or skip a schedule; verify the schedule's time zone behavior, especially on VMs.

Job Step Output to File and Table

For long-term diagnostics, configure the step's Advanced page to write output:

• Output file: a path the Agent service account can write to; the file is appended on each run, with an option to overwrite. Use a UNC path only if the service account has share permissions.
• Log to table: writes the output to msdb.dbo.sysjobstepslogs; view it with sp_help_jobsteplog or by querying the table directly.
• Include step output in history: appends the step's text output to the history row, useful for short outputs but inflates the history log.

On SQL MI, file-based output is constrained because the instance environment does not expose an arbitrary local filesystem in the same way; Log to table is the recommended pattern there.

Agent Error Log

The SQL Server Agent error log is separate from the SQL Server error log and is the first place to look when Agent itself misbehaves. It records service startup and shutdown, schedule evaluations, mail-session errors, and subsystem-level failures. View it in SSMS under SQL Server Agent > Error Logs - not under Management > SQL Server Logs, which shows the engine's error log instead. Cycle it with sp_cycle_agent_errorlog (analogous to sp_cycle_errorlog for the engine). For SQL MI, the equivalent log is surfaced through the platform and SSMS.

Exam Scenario Patterns

A few patterns recur in DP-300 alert/notification questions:

• "No one was paged when the job failed": check whether an operator is assigned to the job's notifications, whether Database Mail is enabled for Agent, and whether a fail-safe operator is configured.
• "Alert never fired": confirm the error is written to the application log; for Sev < 19 errors, RAISERROR ... WITH LOG is required.
• "Notifications stopped after a server restart": the Agent Alert System page's mail profile selection is sometimes reset by a restore or migration; verify the profile binding.
• "Job fails only as Agent but succeeds when run manually": a security context issue - the manual run uses your login, the scheduled run uses the job owner or proxy. Align the security context.