Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If you are using SQL Server Database Mail, your msdb database can grow undesirably. We can use the msdb.dbo.sysmail_delete_mailitems_sp procedure to clear Database Mail History.
Query Database Mail History
We can see the database mail history with the help of the script below.
1 | select * from msdb..sysmail_allitems |
Database Mail History Cleanup
There are two procedure to cleanup database mail history.
- sysmail_delete_mailitems_sp
- sysmail_delete_log_sp
sysmail_delete_mailitems_sp
You can delete database mail messages by using this system stored procedure.
Delete All Mail Messages
1 | EXEC msdb.dbo.sysmail_delete_mailitems_sp |
Delete Mail Messages Before a Certain Date
1 | EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = '2019-01-01' |
Delete Mail Messages With a Certains Status
1 2 | <span class="com">--<span class="tlid-translation translation" lang="en"><span class="" title="">You can also use unsent, retrying, failed instead of sent.</span></span></span> EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'sent' |
Delete All Mail Logs
1 | EXEC msdb.dbo.sysmail_delete_log_sp |
Delete Mail Logs Before a Certain Date
1 | EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = '2019-01-01' |
Delete Mail Logs With a Certains Event Type
1 2 | <span class="com">--<span class="tlid-translation translation" lang="en"><span class="" title="">You can also use </span></span></span><span class="com">warning, error, informational </span><span class="com"><span class="tlid-translation translation" lang="en"><span class="" title="">instead of sent.</span></span></span> EXEC msdb.dbo.sysmail_delete_log_sp @event_type = 'success' |
