Study guide

11.2 Job Alerts, Notifications, and Agent Troubleshooting

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

11.2 Job Alerts, Notifications, and Agent Troubleshooting

Key Takeaways Explain This Topic AI

An operator is a person or distribution list with up to three notification channels: email, pager, and net send; Database Mail must be configured for email notifications to work

SQL Server Agent supports three alert types: SQL Server event alerts (by severity or error number), WMI event alerts, and performance condition alerts

An alert response can execute a job and/or notify one or more operators; the fail-safe operator receives notifications when no operator is defined for an alert or when notification delivery fails

Job history is stored in msdb (sysjobhistory) with configurable retention via Agent properties; the Job Activity Monitor is the live view of currently running and recently finished jobs

Common Agent failures involve proxy credentials, job owner permissions, disabled Agent, a disabled schedule, or msdb issues; troubleshoot using the Agent error log, job step output to file or table, and the step's advanced logging options

Last updated: August 2026

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:

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.

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:

Alert type Trigger Example

SQL Server event alert A specific error number, or any error at or above a severity level, in the application log Sev >= 17 (resource exhaustion), error 823/824 (I/O failures)

WMI event alert A WMI Query Language (WQL) query against the SQL Server WMI provider SELECT * FROM DDL_DATABASE_LEVEL_EVENT to react to schema changes

Performance condition alert A SQL Server performance counter crossing a threshold SQLServer:Locks \ Lock Wait Time > 5000

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:

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.

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.

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).

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.

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.

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.

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.