Quick Scan Report – Alerts

What this check looks for

msdb.dbo.sysalerts, for an alert on each of the severities and error numbers that matter. One finding is raised per missing alert, which is why this check often produces a long list on an instance that has never been configured.

The set covers severity 19 through 25, which are the fatal errors, and the specific error numbers that indicate corruption or I/O trouble: 823, 824 and 825.

Express edition is excluded, because it has no SQL Server Agent and therefore no alerting.

Why it matters

Alerts are the only mechanism that tells you something went wrong at the moment it goes wrong. Everything else in this report is a scan you have to remember to run.

The severities are not arbitrary:

Severity Meaning
19 A fatal error in a resource. The statement is terminated.
20 A fatal error in the current process.
21 A fatal error in the database process, affecting all processes in the database.
22 A fatal error, table integrity suspect. Possible corruption.
23 A fatal error, database integrity suspect. Possible corruption.
24 A hardware error. Usually corruption, usually the storage.
25 A fatal system error.

And the error numbers:

Error Meaning
823 The operating system refused a read or write outright.
824 The read succeeded and the page was wrong: bad checksum or torn page.
825 The read failed and succeeded on a retry.

825 is the one worth configuring an alert for above all the others, because it is the only warning you get before the other two. SQL Server retried, succeeded, carried on, and wrote a single line in the error log that nobody reads. Storage that needs retries is storage that will soon stop retrying successfully. An alert on 825 turns that into an email weeks before the outage.

Without these alerts, corruption is found by DBCC CHECKDB on whatever schedule it runs, or by a user. Both are slower than being told, and the delay is measured against your backup retention.

How to confirm it yourself

SELECT [name], [severity], [message_id], [enabled],
       [has_notification], [delay_between_responses], [last_occurrence_date]
  FROM msdb.dbo.sysalerts WITH (NOLOCK)
 ORDER BY [severity], [message_id];

Which severities have nothing:

SELECT s.[severity]
  FROM (VALUES (19),(20),(21),(22),(23),(24),(25)) AS s([severity])
 WHERE NOT EXISTS (SELECT 1 FROM msdb.dbo.sysalerts AS a WITH (NOLOCK)
                    WHERE a.[severity] = s.[severity] AND a.[enabled] = 1);

An alert with no operator attached does nothing, so check that too:

SELECT a.[name] AS [alert], o.[name] AS [operator], o.[email_address]
  FROM msdb.dbo.sysalerts AS a WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysnotifications AS n WITH (NOLOCK) ON n.[alert_id] = a.[id]
  LEFT JOIN msdb.dbo.sysoperators AS o WITH (NOLOCK) ON o.[id] = n.[operator_id]
 ORDER BY a.[severity];

How to fix it

Three things have to be true before an alert reaches anybody, and missing any one of them makes the other two pointless: Database Mail configured, an operator defined, and the alert created and attached to that operator.

-- one alert per severity
DECLARE @sev INT = 19;
WHILE @sev <= 25
BEGIN
    EXEC msdb.dbo.sp_add_alert
         @name = N'Severity ' + CAST(@sev AS NVARCHAR(2)),
         @severity = @sev,
         @enabled = 1,
         @delay_between_responses = 60,
         @include_event_description_in = 1;

    EXEC msdb.dbo.sp_add_notification
         @alert_name = N'Severity ' + CAST(@sev AS NVARCHAR(2)),
         @operator_name = N'DBA Team',
         @notification_method = 1;
    SET @sev += 1;
END

And the three I/O errors, which are by message id rather than severity:

EXEC msdb.dbo.sp_add_alert @name = N'Error 825 - read retry',
     @message_id = 825, @severity = 0, @enabled = 1, @delay_between_responses = 60,
     @include_event_description_in = 1;
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 825 - read retry',
     @operator_name = N'DBA Team', @notification_method = 1;

Set @delay_between_responses. Without it, an error occurring in a loop sends an email per occurrence, and the first thing anybody does with a mailbox full of identical alerts is switch them off. Sixty seconds is a reasonable floor.

Use a distribution list as the operator, not a person. People leave, and an alert addressed to somebody who left is the same as no alert.

How long it takes

About two hours, most of which is configuring Database Mail and agreeing where the email should go if that has not been decided.

Report Why you would go there
Alerts and Operators Every alert and operator on the instance, and which are wired up.
Email Alert Log Whether anything has actually been sent.
Database Mail Setup The profile the alerts will send through.
Suspect Pages What an 823, 824 or 825 alert would have told you about.
Error Log Where these errors are recorded when nothing alerts on them.
Check
No Operators Configured The other half; an alert with no operator does nothing.
Database Mail Not Enabled Without it, no alert can be delivered.
Agent Jobs without failure notification email The same gap for job failures.
SQL Agent Not Running Nothing alerts at all when Agent is stopped.
Suspected Corruption Exactly what alerts on 823, 824 and 825 exist to catch early.

Frequently asked questions

We have monitoring software. Do we need these? If it watches the error log for these severities and error numbers, no. Confirm that it does rather than assuming, because many tools watch performance counters and service state only.

Will this fill my inbox? Severity 19 to 25 errors should be rare. If they are not rare on your instance, the volume is itself the finding. @delay_between_responses stops a repeating error flooding you.

What about severity 17 and 18? Worth having on a quiet instance, and noisy on a busy one: 17 is a resource problem such as a full database, 18 is a non-fatal internal error. Start at 19 and add lower ones deliberately.

Why is error 825 singled out? Because it is the only one of the three that fires while the problem is still developing. The other two fire when a read has already failed.