Mail Content

Overview

Database Mail History answers whether the mail went. This page answers what was in it, which is a different question with two findings nothing else on the instance can produce.

The Mail Content page: what is actually in the mail this instance sends
The whole page over thirty days. A hundred and ten messages that are four distinct things, holding 3.4MB of content between them.

Where to find it

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

The two findings

The queries

sp_send_dbmail takes a @query, and when it does, that T-SQL runs every time the mail is sent, as the sender, against a database named in the same call.

A nightly mail with a query attached is a scheduled workload that appears in no job, no plan and no schedule, and this table is the only record that it exists. It is also a workload running as whoever called the procedure, against whatever database the call named.

The weight

Attachments and query results are what fill sysmail_attachments, and that table is trimmed by nothing except sysmail_delete_mailitems_sp.

A daily report mailed as a two megabyte attachment is three quarters of a gigabyte a year inside msdb.

Grouped by what the mail is

Forty copies of the same nightly report are one thing sent forty times, and a page that lists them forty times has hidden the twelve other things underneath them.

So rows are grouped by subject and body shape rather than listed by message, with a Times count beside each.

Reading the chart

The bars: messages sent of each kind in the window
Four bars for a hundred and ten messages. The label under each name is the kind, and on this instance nothing sent from here runs a query or carries a file.

One bar per kind of message rather than one per message, because forty copies of the same nightly report are one thing sent forty times and a list of forty rows has hidden the twelve other things underneath them.

The count on the right is that group’s Times column.

Reading the grid

The grid: what is sent, how often, how big, and what is attached
Grouped by subject and body shape, with Times beside each. Attached is the column that explains a large msdb, and here every group is plain text with nothing on it.
Column What it is
What is sent The subject, and the start of the body or the query.
Kind Plain body, body with a query attached, body with files attached, or both.
Profile The profile it goes through.
Times How many messages of this shape were sent in the window.
Last sent The most recent one.
Body The size of the message body.
Attached The total attachment bytes for this group. This is the column that explains msdb.
Verdict Whether this group is a scheduled query, a heavy attachment habit, or an ordinary notification.

Double-click a row to read the whole body and the whole query rather than the part the grid shows.

The toolbar

Control Options Default
Window 7 days, 30 days, a year 30 days
Mail history Opens Database Mail History.
Who gets it Opens Mail Recipients.

Where the data comes from

Source What it gives
dbo.sysmail_allitems Subject, body, body format, the query, the database the query runs in, and the send request timestamp.
dbo.sysmail_attachments Attachment names and sizes per message.
dbo.sysmail_profile The profile name.

Nothing is run. The queries are read and shown, exactly as Job Commands reads job step commands.

Messages you may see

No mail was sent in the last 30 days. Nothing has been put in the post inside this window, so there is no content to read. Database Mail History says what is held in total.

Report Why you would go there
Database Mail History Whether these messages were actually delivered.
Mail Recipients Who receives them.
Database Mail Setup The profile and account behind them.
msdb Space and Retention What sysmail_attachments weighs against the rest of msdb.
Job Commands The other place scheduled T-SQL hides.

Frequently asked questions

A query is attached to a nightly mail. Is that a problem? Not by itself. It is worth knowing about because it is real work on a schedule that appears nowhere else, so it survives every review of the Agent jobs and every migration checklist.

msdb is large and the mail tables are the reason. Look at the Attached column. One report mailed daily as a spreadsheet is the usual cause, and sysmail_delete_mailitems_sp is what removes it.

Why group instead of listing every message? Because the same report sent forty times is one thing. The grouped view puts the twelve other things on the same screen.

Does the page show the query results? No. It shows the query, the body and the attachment sizes. The results were rendered at send time and are only in the attachment bytes.