Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsFor a SQL Server AlwaysOn Availability Group (AG), create SQL Server Agent alerts for message IDs 1480, 35264, and 35265, then test the complete path from SQL Server event to an operator’s inbox. Message 1480 is used for an AG replica-role change; 35264 and 35265 are used for data-movement suspension and resumption. Verify the message text on your SQL Server build, because the historical 2014 guidance has inconsistent labels for the latter two IDs.
This procedure targets SQL Server on Windows with SQL Server Agent and Database Mail. Azure SQL Managed Instance, Linux, and container deployments have different support and logging details; Microsoft documents those differences at SQL Server Agent alerts.
As an Amazon Associate I earn from qualifying purchases.
What these alerts tell you
An AG can move the primary role to another host while applications continue using the same database and listener names. Without notification, operators may not know that a planned or unexpected transition occurred. The alerts below report an event; they do not identify its cause or prove that the application recovered.
Recommended Free Tools
| Purpose | Message ID | Suggested alert name | Interpretation |
|---|---|---|---|
| Replica role change | 1480 | AG Role Change |
Use as a failover or role-transition signal. It does not inherently distinguish planned, automatic, forced, or fault-induced changes. |
| Data movement suspended | 35264 | AG Data Movement - Suspended |
Availability-group data movement stopped. Confirm the event text on the target build. |
| Data movement resumed | 35265 | AG Data Movement - Resumed |
Data movement restarted. This does not mean queues are empty or synchronization is healthy. |
The mapping above follows the historical Part 5 guidance, whose original page was published on September 9, 2014 and now redirects after an ITPro Today migration. Check the current text in sys.messages before production deployment: historical article.
#1 Best Overall
How SQL Server Agent decides to send an alert
- SQL Server raises an event.
- The qualifying event is written to the SQL Server and/or Windows Application log.
- SQL Server Agent compares the event with enabled alert definitions.
- A matching alert notifies an operator by email and can optionally start a SQL Server Agent job.
Having a row in sys.messages is not enough. Microsoft states that only qualifying messages written to the Windows Application log can trigger these alerts. See sp_add_alert documentation.
Prerequisites and topology decisions
- The SQL Server Agent service is running on every instance that can host an AG replica.
- An SQL Server Agent operator exists with a valid email address.
- Database Mail is configured, tested, and available to the SQL Server Agent mail profile.
- The Agent is permitted to use that profile.
- SMTP DNS, firewall, TLS, authentication, and relay rules allow delivery.
- Your account has permission to create alerts; by default,
sysadminmembership is required forsp_add_alert. - Equivalent alerts and operators are deployed on every relevant AG host. Agent alerts are instance-level objects and are not replicated with the availability group.
Configure Database Mail under Management > Database Mail, then set the Agent profile under the SQL Server Agent service properties. Send a direct Database Mail test, but treat it as a separate test: successful Database Mail does not prove that Agent can use the profile or notify the operator.
Create the alerts in SQL Server Management Studio
- Connect to the SQL Server instance in SQL Server Management Studio.
- Expand SQL Server Agent, right-click Alerts, and choose New Alert.
- On General, enter a unique name, select SQL Server event alert, and enter one message number:
1480,35264, or35265. - On Response, select the operator and E-mail. Choose a response job only when you have designed and tested it for this event.
- Save the alert and repeat for the other message IDs.
- Repeat the configuration on each AG instance that must report events.
Alert names are unique within an instance and may be up to 128 characters. Microsoft’s field and alert-type details are documented at learn.microsoft.com/en-us/ssms/agent/alerts.
Create the alerts with T-SQL
Run sp_add_alert and sp_add_notification in msdb. The script below creates the three alerts and includes the event description in notifications.
USE msdb;
GO
-- Replace with the exact existing Agent operator name.
DECLARE @OperatorName sysname = N'DBA Operators';
EXEC dbo.sp_add_alert
@name = N'AG Role Change',
@message_id = 1480,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1;
EXEC dbo.sp_add_notification
@alert_name = N'AG Role Change',
@operator_name = @OperatorName,
@notification_method = 1; -- 1 = e-mail
GO
EXEC dbo.sp_add_alert
@name = N'AG Data Movement - Suspended',
@message_id = 35264,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1;
EXEC dbo.sp_add_notification
@alert_name = N'AG Data Movement - Suspended',
@operator_name = N'DBA Operators',
@notification_method = 1;
GO
EXEC dbo.sp_add_alert
@name = N'AG Data Movement - Resumed',
@message_id = 35265,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1;
EXEC dbo.sp_add_notification
@alert_name = N'AG Data Movement - Resumed',
@operator_name = N'DBA Operators',
@notification_method = 1;
GODo not blindly rerun this script. sp_add_alert creates an object; it is not an upsert. Query existing alerts in SSMS or msdb first, then skip, alter, or fail clearly when a name already exists. Also validate that the operator exists before deployment.
The notification bitmap is 1 for email, 2 for pager, and 4 for net send. Microsoft warns that pager and net send are scheduled for removal, so use email or an external notification system for new designs.
Verify the message IDs on your build
SELECT message_id, severity, language_id, text
FROM sys.messages
WHERE message_id IN (1480, 35264, 35265)
AND language_id = 1033
ORDER BY message_id;- Confirm all three messages exist and record their current text.
- Check that the corresponding event is written to the Windows Application log.
- Ensure each alert is enabled and points to the intended operator.
- Repeat this verification after a major SQL Server upgrade or cumulative update.
Test the complete notification chain
- Send a direct Database Mail test and confirm delivery.
- Use a harmless test alert to verify SQL Server Agent can notify the operator through its configured profile.
- In a test AG or during an approved exercise, perform a controlled role transition or reproduce a data-movement interruption.
- Check the SQL Server error log, Windows Application log, SQL Server Agent history, and Database Mail log.
- Confirm the email includes the host and instance, event description, and AG or database context where available.
- After the role changes, verify delivery from the new primary as well as the former primary.
- Check that repeated suspend/resume events do not create an unmanageable alert storm.
A successful Database Mail test alone does not validate the Agent alert path. The event must be logged, matched by an enabled alert, routed to an operator, accepted by SMTP, and delivered to the recipient.
Free tools Windows power users keep installed
One-click scans. No signup required.
Add severity alerts selectively
The historical article also suggested alerts for severities 17 through 25. These severities can indicate resource or system problems, but workloads differ and false positives are possible. Start only with severities that have an acceptable signal-to-noise ratio.
Rank #3
USE msdb;
GO
EXEC dbo.sp_add_alert
@name = N'Severity 017',
@message_id = 0,
@severity = 17,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1;
GORepeat for other severities only after reviewing your logs. A severity alert uses @severity; a message-number alert uses @message_id. Do not set both as competing criteria for the same definition. The valid severity range for sp_add_alert is 1 through 25.
You may also need separate controls for I/O and storage failures, replication, deadlocks, long-running transactions, and low disk space. Treat these as environment-specific monitoring decisions, not mandatory AG defaults.
When an alert should start a job
An alert can execute a SQL Server Agent job through @job_id or @job_name. A reasonable use is enabling or disabling a tightly scoped group of jobs after a role transition, a pattern discussed in the wider series at Part 28.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchMake the response job:
- Idempotent: running twice produces the same safe state.
- Role-aware: it checks the local replica role and intended AG before changing anything.
- Race-resistant: it tolerates rapid failover and failback.
- Observable: it logs clear success and failure outcomes.
- Pre-deployed: the job exists on every instance that may receive the event.
Never assume a role-change alert identifies the reason for a transition. Correlate it with Windows Failover Clustering events, the SQL Server error log, AG DMVs or dashboard, change records, and application telemetry.
Rank #4
Understand the important limits
- A resumed-data-movement event means movement restarted; it does not prove synchronization state, send and redo queues, listener availability, or application connectivity are healthy.
- A single message-number alert can match events from multiple AGs or databases on one instance. Use event-description filtering, routing, or an external monitor when topology-specific ownership matters.
@event_description_keywordis a literal substring filter; SQLLIKEwildcard patterns are not supported.- Multiple instances, related AG events, and external rules can produce duplicate notifications. Use
@delay_between_responsescarefully so suppression does not hide a real outage. - SQL Server Agent alerts do not continuously measure AG health. Add recurring checks for synchronization state, connection state, send/redo queues, listener health, cluster quorum and lease events, backups, and application availability. See the health-check discussion in Part 18.
Troubleshoot by failure point
Agent does not respond
Confirm the SQL Server Agent service is running, the alert is enabled, and the operator notification exists on that instance.
No alert event appears
Inspect the SQL Server error log and Windows Application log. If the event was not logged, SQL Server Agent cannot match it. Verify the message text and ID on the target build.
Alert exists but no email arrives
Check the operator address, Agent mail profile, Database Mail status and log, SMTP authentication, TLS requirements, firewall, relay policy, and spam quarantine.
Only one node sends notifications
Deploy the operator, mail configuration, alerts, and any response jobs on every relevant AG host. These objects are not synchronized by AlwaysOn.
Best Value
Notifications are duplicated
Identify whether several instances or monitoring systems are reporting the same event. Apply a measured response delay and define which system owns escalation.
Email arrives but the application is unavailable
Continue with listener, DNS, connection-string, cluster, synchronization, and application checks. An event alert is not a service-recovery test.
Built-in alerts versus external monitoring
SQL Server Agent plus Database Mail is included with SQL Server capabilities and is a practical fit for a small number of low-volume emails or a carefully scoped job response. It is a poor fit when you need centralized dashboards, topology-wide correlation, deduplication, on-call escalation, mobile routing, historical analytics, or application-aware recovery validation.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →SQL Server-focused products such as Redgate SQL Monitor and SolarWinds SQL Sentry, or Azure-centric operations through Azure Monitor, can provide those capabilities, but add deployment, security, and licensing overhead. They are optional; the three-message workflow does not require a third-party product.
Quick Recap
Production-readiness checklist
- Target platform and SQL Server version are documented.
- Agent is running on every possible event source.
- Database Mail, Agent profile, SMTP path, and operator address are tested.
- Message IDs 1480, 35264, and 35265 and their current text are verified.
- Alerts are enabled and uniquely named on every relevant instance.
- Event descriptions are included in notifications.
- Any response job is role-aware, idempotent, logged, and present on each host.
- A controlled failover or test event produced an email and an Agent-history entry.
- Operators know that alerts report transitions, not root cause or complete health.
- A separate AG and application-health monitoring process is in place.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
