When you use Database Mail to send emails from SQL Server, email messages and their attachments are stored in the msdb database. You should periodically delete these messages to prevent the database from growing to large.

To delete these messages with T-SQL, use the sysmail_delete_mailitems_sp stored procedure.

You can delete mail messages based on their send request date (i.e. before a certain date) or based on their status. You can also delete all mail messages by using the current date as the request date.

View All Messages

First, let’s see what messages are in the msdb database.

SELECT 
    mailitem_id,
    sent_status,
    send_request_date
FROM msdb.dbo.sysmail_allitems;

Result:

+---------------+---------------+-------------------------+
| mailitem_id   | sent_status   | send_request_date       |
|---------------+---------------+-------------------------|
| 1             | failed        | 2020-08-24 02:40:48.093 |
| 2             | failed        | 2020-08-24 02:47:40.833 |
| 3             | sent          | 2020-08-24 03:58:57.887 |
| 4             | sent          | 2020-08-24 04:11:19.300 |
| 5             | failed        | 2020-08-29 03:28:53.873 |
| 6             | sent          | 2020-08-29 04:00:01.460 |
| 7             | failed        | 2020-08-29 04:44:54.720 |
| 8             | sent          | 2020-08-29 04:45:08.080 |
| 1006          | sent          | 2020-08-30 23:01:20.437 |
+---------------+---------------+-------------------------+

I didn’t return all columns for this view, as there would’ve been too much data to present here.

Delete Old Messages

To delete all messages that were sent before a certain date, use the @sent_before argument.

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp   
    @sent_before = '2020-08-25';

Result:

(4 rows affected)

Note that the sysmail_delete_mailitems_sp procedure actually deletes emails based on the send_request_date rather than the sent_date. That’s why I’m using send_request_date when viewing the email messages.

View All Messages Again

Now when I query the sysmail_allitems view, the first four rows have disappeared.

SELECT 
    mailitem_id,
    sent_status,
    send_request_date
FROM msdb.dbo.sysmail_allitems;

Result:

+---------------+---------------+-------------------------+
| mailitem_id   | sent_status   | send_request_date       |
|---------------+---------------+-------------------------|
| 5             | failed        | 2020-08-29 03:28:53.873 |
| 6             | sent          | 2020-08-29 04:00:01.460 |
| 7             | failed        | 2020-08-29 04:44:54.720 |
| 8             | sent          | 2020-08-29 04:45:08.080 |
| 1006          | sent          | 2020-08-30 23:01:20.437 |
+---------------+---------------+-------------------------+

Delete Failed Messages

To delete all messages of a certain status, use the @sent_status argument.

Here’s an example of deleting all failed mail messages.

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp   
    @sent_status = 'failed';

Result:

(2 rows affected)

View All Messages Again

Let’s check the sysmail_allitems view again.

SELECT 
    mailitem_id,
    sent_status,
    send_request_date
FROM msdb.dbo.sysmail_allitems;

Result:

+---------------+---------------+-------------------------+
| mailitem_id   | sent_status   | send_request_date       |
|---------------+---------------+-------------------------|
| 6             | sent          | 2020-08-29 04:00:01.460 |
| 8             | sent          | 2020-08-29 04:45:08.080 |
| 1006          | sent          | 2020-08-30 23:01:20.437 |
+---------------+---------------+-------------------------+

Delete All Messages

To delete all messages, use the @sent_date argument with a date value of GETDATE().

DECLARE @GETDATE datetime  
SET @GETDATE = GETDATE();
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
    @sent_before = @GETDATE;

Result:

(3 rows affected)

View All Messages Again

Let’s check the sysmail_allitems view again.

SELECT 
    mailitem_id,
    sent_status,
    send_request_date
FROM msdb.dbo.sysmail_allitems;

Result:

(0 rows affected)

Delete Events from the Database Mail Log

Note that sysmail_delete_mailitems_sp does not delete the corresponding entries in the Database Mail log. Use sysmail_delete_log_sp to delete events from the Database Mail log.