Alerts and Operators

Overview

Alerts are the part of SQL Server that is only ever tested by the thing it was meant to catch. An instance can have nine alerts, three operators and a mail profile and still tell nobody anything, because every one of the joins between them is optional and none of them complains when it is missing.

So this page is built around coverage, not inventory.

The Alerts and Operators page: coverage rather than inventory
The whole page. Seventeen alerts, no operators, and ten of the recommended alerts not configured at all, with every bar red because none of this would reach a person tonight.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Alerts and Operators.

Shown only on an instance that can have SQL Server Agent.

The nine alerts every instance should have

These are known, so an alert that is not there is listed anyway, as a row with nothing in it:

Alert What it catches
Severity 19 Fatal error in a resource.
Severity 20 Fatal error in the current process.
Severity 21 Fatal error in database processes.
Severity 22 Table integrity suspect.
Severity 23 Database integrity suspect.
Severity 24 Hardware error.
Severity 25 Fatal system error.
Error 823 The operating system refused a read or a write.
Error 824 A page was read and was wrong.
Error 825 A read succeeded only after being retried. The quiet one, and the early warning.

A query over sysalerts cannot list an alert that is not there, which is why the missing ones are drawn as empty rows. A page that can only show what exists cannot answer the question somebody opened it to ask.

The four ways an alert that exists still tells nobody

Way How it looks
It is disabled. It counts occurrences and does nothing with them.
It notifies nobody. sysalerts has a has_notification column and there are no rows in sysnotifications.
The operator is disabled, or has no email address. The alert fires, the notification is addressed to nothing.
The operator has an address and Database Mail cannot send. A different page, and this one links to it.

The fail safe operator

Read out of the registry the way SSMS reads it, because Agent keeps it there rather than in msdb. It is the operator that gets told when everything else on this list has failed, and on most instances it is not set.

Reading the chart

The bars: every alert worth having, including the ones that do not exist
Three missing alerts at the top, then the ones that exist and are disabled, then the ones that fire and notify nobody. An instance with real coverage has none of these bars.

One bar per alert, including the recommended ones that are not there, with the reason this row tells nobody on the right: not configured, disabled, or tells nobody.

A bar is red when nothing about this alert would reach a person, which is the same test the Verdict column applies in the grid. Drawing it means a covered instance and an uncovered one look different without reading a word.

The bars draw the top thirteen of the twenty seven. The grid holds them all.

Reading the grid

The grid: what each alert is, who it reaches and whether it would tell anybody
Reaches is the column that decides everything, and blank is the finding. A recommended alert that does not exist gets a row with its name, nothing else, and a verdict saying so.
Column What it is
Name The alert or operator.
What it is Severity alert, error number alert, performance condition alert, WMI alert, or operator.
Enabled Whether it is switched on.
Reaches Who this alert notifies, with the method. Blank is the finding.
Times The occurrence count Agent keeps.
Last The last occurrence.
Response job The job this alert starts, when it starts one.
Verdict One sentence: whether this row would tell anybody anything tonight.

A missing recommended alert appears with its name, nothing else, and a verdict saying it does not exist.

The toolbar

Control What it does
Mail setup Opens Database Mail Setup.
Agent activity Opens Agent Activity.
Failed jobs Opens the Failed Jobs report.

Where the data comes from

Source What it gives
dbo.sysalerts The alerts, their severity or error number, enabled state, occurrence count and last occurrence.
dbo.sysoperators The operators, their addresses, their enabled state and their pager schedules.
dbo.sysnotifications Which operator each alert notifies, and by which method.
dbo.sysjobs The response job, when there is one.
xp_instance_regread The fail safe operator.

The Agent time encoding again. last_occurrence_date and last_occurrence_time are integers holding yyyymmdd and hhmmss, and neither can be compared or subtracted as a number.

Report Why you would go there
Database Mail Setup Whether the mail behind these operators works at all.
Database Mail History Whether anything has actually been sent.
Mail Recipients Who is on the receiving end, including the addresses no operator owns.
Agent Settings The fail safe operator and Agent’s own mail profile.
Failed Jobs What has been failing without anybody hearing about it.

Frequently asked questions

Why list alerts that do not exist? Because that is the finding. An instance with no severity 24 alert will not tell anybody about a hardware error, and no query over the alerts that do exist can say so.

The alert has fired a hundred times and nobody got mail. Check the Reaches column. An alert with a notification count and no operator behind it has been counting quietly for however long it has existed.

Why is error 825 on the list? It is only a warning. Because it is the only one that arrives before the outage. 825 means a read failed and succeeded on a retry, which is a disk telling you what it is about to do.

We use a monitoring product, so we do not need these. Then the page is a checklist you can ignore in one glance. It is still worth knowing that the instance itself would say nothing.