SQL Agent Alerts for Severity 17 to 25 and Errors 823, 824, 825

A serious database error is less useful as a morning discovery than as a timely notification. Configure Agent alerts with a tested mail route and an accountable operator so the event reaches someone able to respond.

Build the Entire Notification Route

The alert definition is only one link. SQL Server must produce a qualifying logged event, and SQL Server Agent must detect it. The alert must be enabled, an operator notification must exist, and the mail path must deliver the message. Verify each link rather than equating successful alert creation with operational coverage.

I test the delivery route before creating a large collection of definitions. An unreachable operator address and a stopped Agent service can leave perfectly written alert scripts silent. Define who responds, how the notification is escalated, and where the incident instructions live. A warning that reaches an unmonitored mailbox has technically traveled and practically gone nowhere.

Use an edition that includes SQL Server Agent; Express does not provide it. Configure administrative alerts through an approved sysadmin session and review existing definitions before using these fresh sample names. The examples demonstrate setup, not an idempotent update of an established alert system.

Configure Database Mail and the Agent Profile

Use an approved SMTP relay, encryption settings, and sender identity. The sample uses deliberately non-deliverable example addresses and a placeholder relay name, so replace them with accepted configuration before testing. The account example assumes the approved relay permits this sender without a password in the script.

USE msdb;
GO
EXEC dbo.sysmail_add_account_sp
    @account_name=N'DBA Alert Account',
    @email_address=N'sql-alerts@example.invalid',
    @display_name=N'SQL Server Alerts',
    @mailserver_name=N'ApprovedMailRelay',
    @port=587,@enable_ssl=1;
EXEC dbo.sysmail_add_profile_sp @profile_name=N'DBA Alert Mail';
EXEC dbo.sysmail_add_profileaccount_sp
    @profile_name=N'DBA Alert Mail',
    @account_name=N'DBA Alert Account',@sequence_number=1;
EXEC dbo.sp_set_sqlagent_properties
    @use_databasemail=1,@databasemail_profile=N'DBA Alert Mail';

Enable Database Mail XPs through the approved instance configuration process if necessary. Grant only the required profile access, and configure relay authentication securely when the accepted mail service requires it. Do not put a production password into a shared article script. Restart SQL Server Agent in a reviewed window after changing its mail configuration, then verify the active profile.

Send a controlled Database Mail test to the accepted operator address and confirm actual receipt. A queued or sent status is useful evidence about one stage, but recipient confirmation validates the destination. Retain failure details from the mail log when that check does not complete.

Create the Operator and Severity Agent Alerts

An operator supplies the notification destination; it does not create an alert by itself. Create the named operator, then one alert per severity from seventeen through twenty-five. The response delay is measured in seconds and controls repeated responses from an alert, rather than removing the underlying logged events.

USE msdb;
GO
EXEC dbo.sp_add_operator
    @name=N'DBA OnCall',@enabled=1,
    @email_address=N'dba-oncall@example.invalid';
DECLARE @Severity int=17,@AlertName sysname;
WHILE @Severity<=25
BEGIN
    SET @AlertName=CONCAT(N'DBA Severity ',@Severity);
    EXEC dbo.sp_add_alert
        @name=@AlertName,@message_id=0,@severity=@Severity,
        @enabled=1,@delay_between_responses=900,
        @include_event_description_in=1;
    EXEC dbo.sp_add_notification
        @alert_name=@AlertName,@operator_name=N'DBA OnCall',
        @notification_method=1;
    SET @Severity+=1;
END;

Choose the delay from the response process. Fifteen minutes is an example throttle, not a requirement for every event. A delay can prevent one repeated fault from filling an inbox, but it also changes how frequently a continuing fault reminds the operator. Keep the first notification and escalation policy timely.

Understand the Severity Ranges

Severity seventeen identifies resource exhaustion or a configured resource boundary, while eighteen indicates an engine software problem that does not automatically terminate the connection. Nineteen represents a more serious engine limitation that interrupts the batch. Levels twenty through twenty-five indicate fatal conditions that can terminate the connection and require administrator investigation.

Severity conveys a category, not a complete diagnosis or automatic repair instruction. Read the actual error number, state, affected database, and surrounding log entries. Some conditions are scoped to a task or database, while others concern broader resources. Do not turn every high-severity alert into an automatic instance restart.

For severity below nineteen, confirm that the event is actually written to the Windows application log. A severity-based Agent definition cannot detect an event that never reaches its required logging path. Use supported logging configuration for approved messages rather than assuming a severity label alone guarantees notification.

Add Agent Alerts for Errors 823, 824 and 825

Create message-number alerts for 823, 824, and 825 in addition to the severity coverage. An 823 indicates an operating-system-level I/O failure. An 824 indicates a logical consistency problem detected after I/O. An 825 records a read that succeeded only after retry, making it an important warning despite its lower severity.

USE msdb;
GO
DECLARE @Messages TABLE (MessageID int PRIMARY KEY);
INSERT @Messages VALUES (823),(824),(825);
DECLARE @MessageID int,@AlertName sysname;
DECLARE MessageList CURSOR LOCAL FAST_FORWARD FOR
    SELECT MessageID FROM @Messages ORDER BY MessageID;
OPEN MessageList;
FETCH NEXT FROM MessageList INTO @MessageID;
WHILE @@FETCH_STATUS=0
BEGIN
    SET @AlertName=CONCAT(N'DBA Error ',@MessageID);
    EXEC dbo.sp_add_alert
        @name=@AlertName,@message_id=@MessageID,@severity=0,
        @enabled=1,@delay_between_responses=900,
        @include_event_description_in=1;
    EXEC dbo.sp_add_notification
        @alert_name=@AlertName,@operator_name=N'DBA OnCall',
        @notification_method=1;
    FETCH NEXT FROM MessageList INTO @MessageID;
END;
CLOSE MessageList;
DEALLOCATE MessageList;

Review overlapping severity and message coverage during testing so the response process understands which definition handles each event. Do not treat successful retries as evidence that the storage path is healthy. Preserve the full 825 message and investigate the storage stack, related errors, and database integrity through the accepted incident plan.

Confirm the Built-In Messages and Definitions

Inspect documented message metadata and the configured alert population. This verifies the target instance's severities and logging flags without manufacturing a storage failure. Never damage a database to test notification coverage for these error numbers.

SELECT message_id,severity,is_event_logged,text
FROM sys.messages
WHERE message_id IN (823,824,825) AND language_id=1033;
SELECT name,message_id,severity,enabled,delay_between_responses
FROM msdb.dbo.sysalerts
WHERE name LIKE N'DBA Severity %' OR name LIKE N'DBA Error %';

If an expected message needs additional logging configuration, have the administrator review the supported sp_altermessage process and its operational impact. Keep that decision explicit. Also confirm the notification rows and operator state; a correct alert predicate without an attached destination is incomplete coverage.

Test Agent Alerts With a Benign Logged Event

A controlled rehearsal can raise a logged severity-seventeen message to test the severity path. Use a nonproduction instance and an accepted test window. WITH LOG requires appropriate privilege, and this statement deliberately reports an error, so do not place it inside an unrelated application batch.

RAISERROR(N'Controlled severity-17 notification test.',17,1) WITH LOG;

I confirm the event, Agent response, mail delivery, and operator acknowledgment separately. Which step would reveal a stopped mail route tomorrow? Add a recurring health check for the notification system and review the recipient when the on-call arrangement changes. Agent alerts provide useful coverage when detection, delivery, and ownership remain tested together.

Keep Agent alerts in the operational inventory with their tested delivery route and assigned owner. Review notification delays after a real incident and keep event counts in the diagnostic record. A throttled mailbox should not conceal the fault's frequency from the responder. Retain repeated log evidence, check whether the same storage path affects multiple databases, and verify that the escalation reaches the current responsible person. Delivery testing needs maintenance whenever identities, profiles, relay policy, or service configuration change.

Related reading on this blog: SQL Agent Alerts Every Instance Should Have and Setting Up Database Mail for Alerts.

An alert definition is not operational coverage, it is one link in a tested detection and response route.

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