Database Mail History

Overview

Database Mail is how an instance tells somebody that a job failed or a database is out of space, which makes it the one piece of infrastructure whose own failure is silent by construction: the mechanism that would have told you it is broken is the thing that is broken.

The Database Mail History page: what was sent, what failed and what is still waiting
The whole page over seven days. Eight mail items, none of them failed, and fourteen log entries that belong to the queue itself rather than to a message.

Where to find it

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

Four states, and all four matter

Status What it means
Sent The boring one.
Failed The loud one.
Retrying The SMTP server refused it and the queue has not given up yet.
Waiting to send The quiet one, and the one worth knowing about. Nothing has tried. On an instance whose mail queue has been stopped, this is where every message since then is sitting, looking exactly like a network problem.

Why it is drawn as lanes

Mail failures come in bursts. A mail server that was down for two hours produces sixty failures in a row, which a list shows as sixty rows and a lane shows as one outage.

  • One lane per profile, one mark per message.
  • A point for a message, a bar for one whose send took measurable time.
  • A span shorter than three pixels is still drawn three pixels wide, so a real event never vanishes.
  • The axis is labeled at both ends and in the middle, with a format chosen from the window.

The log is read as well as the queue

Rows in sysmail_log with no mail item attached are the instance level ones: the queue stopping, the activation failing, the service account that cannot reach the SMTP host.

Those explain a page full of unsent mail better than any of the mail items can, and they appear in the What the mail log said column and in the summary above the chart.

Reading the chart

The lanes: one per profile, one mark per message, placed when it was asked for
Two marks, both sent. A mail server that had been down for two hours would show here as one dense group of failures rather than as sixty separate rows.

One lane per profile and one mark per message, placed at the moment sp_send_dbmail was called rather than when the message went.

That is what makes a burst readable. The gap between the mark and the send is the queue, and a long gap with a successful send at the end of it is a queue backing up rather than a mail problem.

Reading the grid

The grid: subject, status, who asked for it and what the mail log said
Messages first, then the rows from sysmail_log with no mail item behind them. Those are the instance level ones, and here they are the mail queue stopping and starting.
Column What it is
Subject The message subject.
Status Sent, failed, retrying, or waiting to send.
Profile The profile it went through.
To The recipients, including copy and blind copy.
Asked for When sp_send_dbmail was called.
Sent When it actually went, which is blank for everything that has not.
Took The gap between the two. A long gap with a successful send is a queue backing up.
Asked by The principal that called sp_send_dbmail.
What the mail log said The matching sysmail_log text, which is where the SMTP error is.

The toolbar

Control Options Default
Window 24 hours, 7 days, 30 days, a year 7 days
Mail setup Opens Database Mail Setup.
Space and retention Opens msdb Space and Retention.

Where the data comes from

Source What it gives
dbo.sysmail_allitems Every message, its status, its recipients, its subject and the two timestamps.
dbo.sysmail_log The mail service’s own log, including the rows that belong to no message.
dbo.sysmail_faileditems The failures, for the status split.
dbo.sysmail_profile The profile names for the lanes.

sysmail_mailitems is trimmed by nothing except sysmail_delete_mailitems_sp. On an instance where nobody runs it, this table and sysmail_attachments are often most of msdb.

Messages you may see

This instance has never sent a mail. sysmail_mailitems is empty. Either Database Mail has never been used here, or somebody has trimmed every row out of it.

No mail was sent in the last 7 days. With a count of what is held here in total, so a quiet week reads differently from an empty table.

Report Why you would go there
Database Mail Setup Whether the profile could send at all.
Mail Recipients Who has been receiving this, and who has not.
Mail Content What is in the messages, including queries and attachments.
Alerts and Operators Whether anything is wired to send mail in the first place.
msdb Space and Retention What the mail tables weigh.

Frequently asked questions

Everything is “waiting to send”. The Database Mail queue is not running. sysmail_start_sp starts it, and the sysmail_log rows on this page usually say why it stopped.

Mail is sent but nobody receives it. Then it left SQL Server and the SMTP server accepted it. The next place to look is the mail server, not this instance. Mail Recipients will tell you which addresses have ever been used.

Why does “Took” show minutes on a message that succeeded? The queue was backed up. Database Mail sends asynchronously, so a long gap between asked-for and sent is a throughput problem rather than a failure.

How far back does this go? As far as nothing has trimmed it, which on most instances is to the day Database Mail was configured.