Mail Recipients

Overview

The address list of a SQL Server is nowhere.

Operators have one address each and are the list people think of, but most mail on a typical instance goes to addresses passed straight to sp_send_dbmail by a script, and those appear in no dialog anywhere. The only record is the recipients column of every message ever sent.

The Mail Recipients page: the address list of the instance
The whole page over a year. Two addresses across two domains for 380 messages, neither of them belonging to an operator.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Mail Recipients.

Three findings come out of turning that into a list of people

The leaver

Somebody who left two years ago is still getting the nightly failure mail, and nobody will find that out until the mail server starts rejecting it.

The operator who receives nothing

An operator with an address that has never appeared on a sent message either has no alert wired to it, or its alerts have never fired. Those are worth telling apart before an incident rather than during one.

The address that is not an operator at all

Anything on this list that Agent does not know about is somebody a script decided to mail, and a script is where it has to be changed.

Reading the chart

The bars: messages sent to each address over the window
Two addresses, 379 messages to one of them. Neither belongs to an operator, so neither appears in any dialog in SSMS.

One bar per address, ranked by the messages sent to it, with what the page knows about that address under the name.

Whether an operator owns the address is the line that matters. An operator is a list somebody maintains; anything else here was chosen by a script, and a script is where it has to be changed.

Reading the grid

The grid: one row per address, with the operator, the traffic and the failures
One row per address however it got there. An address that fails every time is usually a mailbox that no longer exists, and both verdicts here say a script is doing the sending.
Column What it is
Address One row per address, however it got there.
Operator The operator that owns this address, or blank when no operator does.
Messages How many messages went to it in the window.
Failed How many of those failed. An address that fails every time is usually a mailbox that no longer exists.
First / Last The span over which this address has been used.
How To, copy, blind copy, or a mixture.
Verdict Operator with traffic, operator with none, or an address no operator owns.

How the addresses are split

The three recipient columns are semicolon separated lists in a varchar(max), so they are split in the application rather than in the query.

STRING_SPLIT is SQL Server 2016 and this product still opens 2005 instances, and a recursive common table expression to do it would cost more than reading the column and splitting a string.

The toolbar

Control Options Default
Window 30 days, a year, everything a year
Mail content Opens Mail Content.
Alerts and operators Opens Alerts and Operators.

Where the data comes from

Source What it gives
dbo.sysmail_allitems recipients, copy_recipients, blind_copy_recipients, the status and the send timestamp.
dbo.sysoperators The operator names and their email addresses, for the join.
dbo.sysmail_faileditems Which messages to an address failed.

Messages you may see

Nobody has been sent anything from this instance. No mail inside the window and no operator with an address. If an alert fires here, it has nowhere to go.

Report Why you would go there
Alerts and Operators The operator side of the list, and whether anything is wired to fire.
Database Mail History What was sent, and what happened to it.
Mail Content What was in it.
Database Mail Setup Whether mail works at all.

Frequently asked questions

An address has a hundred messages and a hundred failures. The mailbox is gone. Everything addressed to it has been bouncing, and the alerts that use it have effectively been off for however long that is.

An operator shows zero messages. Either no alert notifies it, or no alert that notifies it has ever fired. Alerts and Operators separates the two.

Where do the addresses that are not operators come from? Scripts calling sp_send_dbmail with a literal address. Mail Content shows which message shapes those are, and the script is wherever that message is composed.

Why does “everything” take longer? Because it reads every row of sysmail_allitems, which on an instance that has never been trimmed can be millions.