The SQL Server Agent alerts every instance should have: severity 17–25 and errors 823, 824 and 825

#Why these alerts

Many serious problems are written to the error log and nowhere else. Agent alerts watch the log and email you within a minute.

#Prerequisites

Database Mail is configured, Agent uses a mail profile, and an operator exists:

EXEC msdb.dbo.sp_add_operator @name = N'DBA Team', @email_address = N'dba-team@example.com';

#Create the alerts

USE msdb;
DECLARE @op sysname = N'DBA Team', @sev int = 17, @name sysname;

WHILE @sev <= 25
BEGIN
  SET @name = N'Severity ' + CAST(@sev AS nvarchar(2));
  IF NOT EXISTS (SELECT 1 FROM dbo.sysalerts WHERE name = @name)
  BEGIN
    EXEC dbo.sp_add_alert @name = @name, @severity = @sev, @enabled = 1,
         @delay_between_responses = 300, @include_event_description_in = 1;
    EXEC dbo.sp_add_notification @alert_name = @name, @operator_name = @op, @notification_method = 1;
  END
  SET @sev += 1;
END;

DECLARE @err TABLE (id int); INSERT @err VALUES (823),(824),(825);
DECLARE @id int;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT id FROM @err;
OPEN c; FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @name = N'Error ' + CAST(@id AS nvarchar(5));
  IF NOT EXISTS (SELECT 1 FROM dbo.sysalerts WHERE name = @name)
  BEGIN
    EXEC dbo.sp_add_alert @name = @name, @message_id = @id, @severity = 0, @enabled = 1,
         @delay_between_responses = 300, @include_event_description_in = 1;
    EXEC dbo.sp_add_notification @alert_name = @name, @operator_name = @op, @notification_method = 1;
  END
  FETCH NEXT FROM c INTO @id;
END
CLOSE c; DEALLOCATE c;

@delay_between_responses = 300 sends at most one email per alert every five minutes. Without a delay, one problem can send thousands of emails.

#Test safely

Error 50000-range user messages won't trigger severity alerts unless logged. Raise a test error with logging at a severity that won't harm anything:

RAISERROR (N'Agent alert test - please ignore', 17, 1) WITH LOG;

Severity 19 and above require sysadmin and WITH LOG. Severity 20+ terminates the connection, so test at 17 or 18 only.

Check alert history:

SELECT name, occurrence_count, last_occurrence_date, last_occurrence_time, last_response_date
FROM msdb.dbo.sysalerts ORDER BY name;

#When one fires

Warning

An 825 alert is a gift: storage is failing but data is still readable. Act before it becomes 824.

#Prevent it next time

• Include these alerts in your server build script.
• Review suspect_pages weekly.
• Make sure page verification is CHECKSUM on all databases.