Excessive sysmail_allitems

What this check looks for

The row count of msdb.dbo.sysmail_allitems, compared against a threshold that defaults to 2,000 rows.

The threshold is configurable. If a DBHealthHistory database exists, the check reads ignoreLessThanCheck105Rows from its Settings table and uses that value instead, so an instance that legitimately sends a great deal of mail can raise the bar rather than living with a finding that never goes away.

The check requires sysadmin and is skipped on Amazon RDS.

Why it matters

Database Mail keeps everything, forever, and nothing cleans it up.

Every message SQL Server has ever sent is still in msdb: the recipients, the subject, and the full body. There is no retention setting, no default cleanup job and no maximum. The only thing that removes any of it is a stored procedure somebody has to schedule.

That has three consequences:

• msdb grows. Steadily, invisibly, and in a database that most people never look at because it is a system database. On an instance with a chatty alerting setup, a few thousand rows a week becomes a very large table over a few years.
• msdb backups grow with it. They are backing up years of email bodies.
• It is a quiet data retention problem. Alert emails routinely quote query text, server names, file paths, row counts and occasionally data. That content is sitting in a system database that is rarely reviewed, often more widely readable than the databases it describes, and copied into every msdb backup.

There is a fourth, smaller one: sysmail_allitems is a view over sysmail_mailitems, and the Database Mail troubleshooting queries everyone uses read it. When it holds hundreds of thousands of rows, those queries get slow, which is exactly when you are trying to work out why an alert did not arrive.

How to confirm it yourself

SELECT COUNT(*)                AS [total_items],
       MIN([send_request_date]) AS [oldest],
       MAX([send_request_date]) AS [newest]
  FROM msdb.dbo.sysmail_allitems WITH (NOLOCK);

SELECT [sent_status], COUNT(*) AS [items]
  FROM msdb.dbo.sysmail_allitems WITH (NOLOCK)
 GROUP BY [sent_status]
 ORDER BY [items] DESC;

How much space it is actually taking:

SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name],
       t.[name]                          AS [table_name],
       SUM(p.[rows])                     AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(10,1)) AS [size_mb]
  FROM msdb.sys.tables AS t WITH (NOLOCK)
 INNER JOIN msdb.sys.indexes AS i WITH (NOLOCK)
         ON i.[object_id] = t.[object_id]
 INNER JOIN msdb.sys.partitions AS p WITH (NOLOCK)
         ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
 INNER JOIN msdb.sys.allocation_units AS a WITH (NOLOCK)
         ON a.[container_id] = p.[partition_id]
 WHERE t.[name] LIKE 'sysmail%'
 GROUP BY t.[object_id], t.[name]
 ORDER BY [size_mb] DESC;

How to fix it

The Quick Scan offers this directly. Right click the finding in the scan report and choose one of the built in actions, which clear items older than 30 days, 90 days or one year.

The statement behind them is the supported cleanup procedure:

DECLARE @cutoff DATETIME = DATEADD(DAY, -90, GETDATE());

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @cutoff;

Then schedule it, because this will simply happen again otherwise. A weekly Agent job running the same statement is the whole solution, and it is the step most instances are missing rather than the initial cleanup.

Two practical notes:

• Delete in batches on a large table. A single delete of several hundred thousand rows takes a long transaction and a great deal of log in msdb. Work backwards a month at a time.
• Clear the event log too. sysmail_delete_log_sp removes the Database Mail event log, which grows alongside and is not covered by the mail item cleanup:

EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @cutoff;

Decide the retention deliberately. Ninety days is a common answer: long enough to investigate a missed alert, short enough that the table stays manageable. If you need longer for a reason, raise the threshold this check uses rather than ignoring it.

How long it takes

About two hours, most of which is deleting in batches on an instance where this has been accumulating for years. Scheduling the job takes minutes.

Related reports

Related checks

Frequently asked questions

Why 2,000 rows? It is a low bar deliberately, because on most instances a healthy Database Mail setup sends far fewer than that in a retention period. If yours legitimately sends more, set ignoreLessThanCheck105Rows in the DBHealthHistory Settings table to a number that suits you.

Is it safe to delete? Yes. sysmail_allitems is history, not queue. Mail waiting to be sent lives in sysmail_unsentitems, and sysmail_delete_mailitems_sp will not remove anything that has not been processed.

Can I just truncate the table? Do not. Use the supplied procedure. The mail tables are related and the procedure removes the attachments and related rows with the items.

We need the history for auditing. Then keep the period you actually need and delete beyond it, rather than keeping everything by default. Unbounded is not a retention policy.