SQL Agent Alerts Every Instance Should Have

The first sign of an I/O problem should not be a customer call. SQL Agent alerts can route important engine events to a person who can act. Configure the alert path once, then test and review it like any other recovery tool.

Decide Which Events Need Action

Start with high-severity errors and the I/O-related errors 823, 824, and 825. Error 823 and 824 indicate serious read or consistency problems. Error 825 records a read retry that eventually succeeded, which still deserves investigation. Route these to a staffed response path. Severity alerts from 16 through 25 need review by severity and event logging behavior so the team does not receive a storm of routine application errors.

I ask what the recipient should do after each message. If the answer is “ignore most of them,” narrow the alert. Which failure would you want to hear about during an otherwise quiet weekend? Build the initial set around that answer, then expand with evidence. Write the response owner beside each alert so the message has a clear destination.

Confirm Agent and Mail Are Ready

SQL Server Agent must be running for its alerts to act. Configure Database Mail with an account and profile, then enable the selected mail profile in SQL Server Agent Alert System settings. Restart Agent if the configuration procedure requires it. Create an operator with an address owned by a team or on-call route, not a single person’s old mailbox.

I send a test mail before testing an alert. That isolates SMTP and profile problems from alert definition problems. A green alert object is not evidence that an email reached the operator. Check the Database Mail queue and recipient inbox, then record the test result. The shortest alert chain is the one you have actually exercised.

Inventory Existing SQL Agent Alerts

Before adding anything, inspect current alerts for duplicate names, severities, error numbers, enabled state, and notification delay. The query below reads msdb alert definitions. Compare them with the runbook and the operators they notify. A duplicate alert can produce two pages for one event, while a disabled one produces none.

I keep a simple inventory per instance because alert setup drifts during migrations. A server build script can create the alert while a mail profile remains absent. Querying both objects exposes the mismatch. Do not assume the new instance inherited alert configuration from a restored user database.

SELECT
    name,
    enabled,
    severity,
    message_id,
    delay_between_responses
FROM msdb.dbo.sysalerts
ORDER BY name;

Treat Severity 16 SQL Agent Alerts With Care

Severity 16 includes user-correctable and application-level errors. A broad alert on every such event can flood the on-call team if those errors are logged. Alert on specific error numbers or a narrowly justified pattern when the action is clear. Severities 17 through 25 deserve a defined response, but confirm which events are written to the Windows Application log and therefore reach Agent event alerts.

I review actual event history before broadening a severity rule. An alert that fires constantly teaches people to stop reading. That is a monitoring failure even if the configuration is technically enabled. Keep response delays and escalation rules intentional.

Give 823, 824 and 825 Their Own Path

Define alerts by message number for 823, 824, and 825 so these storage warnings do not disappear inside a generic severity inbox. Include instance name, database context where available, time, and a link to the incident runbook in the notification process. The first response should capture the SQL Server error log, storage evidence, backup state, and integrity check plan under the organization’s incident rules.

Do not generate these errors as a test. Use controlled harmless test events for mail delivery, then verify the real alert definitions by inspection. I keep the response checklist beside the alert names. A page without a next step is only a louder log entry.

Check Operators and Notifications

An operator can be disabled, have an old address, or never be attached to the alert. The next query lists operator names and enabled state. Review email destinations through the protected configuration interface and confirm each alert has the intended notification method. Use a shared on-call route where staffing changes are expected.

I test after a staff change or mail provider change. The SQL configuration can remain unchanged while delivery breaks elsewhere. Keep an independent monitoring path for a stopped SQL Server Agent service, because Agent cannot alert you through itself when it is down. That blind spot belongs in the operations plan.

SELECT
    name,
    enabled,
    email_address
FROM msdb.dbo.sysoperators
ORDER BY name;

Test the End-to-End Route

Use a safe, documented test alert or operator test procedure in a nonproduction or controlled production window. Confirm that the event is logged, Agent sees it, Database Mail sends it, and the recipient receives it. Record the time and outcome. If delivery fails, inspect sysmail_allitems and sysmail_event_log and separate mail transport failure from alert matching failure.

I do not mark the setup complete when sp_send_dbmail alone works. That proves only part of the chain. The actual event-to-operator path is what matters. The test should also show that the message contains enough context for someone who did not set up the server.

Keep the SQL Agent Alerts Current

Review alerts after upgrades, new databases, staff changes, and incident lessons. Remove duplicate or unactionable messages, update routing, and retest. Track alerts as instance configuration so a rebuild can recreate them. Keep the error-specific response guide close to the operator schedule.

These SQL Agent alerts are small pieces of work done once and maintained. I include them in every new instance acceptance checklist. The setup is easy to postpone because nothing looks broken on a quiet day. A monthly delivery test gives the team evidence that the route still works after mail and staffing changes. That is exactly when it is easiest to test it properly.

Related reading on this blog: SQL Server Alert Management: From Chaos to Clarity and Error: 825: A Read of the File at Offset Succeeded After Failing 1 Time(s).

An alert is not a safety net because it exists, it is one when the right person reads it in time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.