How to delete SQL Server Database Mail history WITHOUT stored procedures

Question

This is really straight forward. The documented way to delete Database mail history in SQL server is to use the following two stored procedures:sysmail_delete_mailitems_sp and sysmail_delete_log_sp

Great. But what happens when apparently billions of emails are stored in here taking up several hundred gigabytes in msdb, and even the process of trying to clear only 100 days worth of email takes hours or days?

I don't want any history and I don't want to wait until I'm a senior citizen to clear the list of e-mail history. How do I truncate this history in one quick transaction?

So far my efforts at using the built in procedures have been futile - growing tempdb and msdb to terabytes in size and sitting "executing" for hours on end. Any attempt to cancel it results in the entire transaction being rolled back. It so far hasn't even been possible to count the number of emails that exist in the history - it's that bad.

score 3 · Answer 1 · answered May 30 '19 at 01:19

3

One idea could be:

script out CREATE TABLE for sysmail_attachments, sysmail_send_retries, sysmail_mailitems, and sysmail_log
start a transaction
drop the tables in that order
create the tables again using the script from 1.
commit the transaction
create a job that purges those tables regularly (like, once an hour, from the sounds of it), also in that order

answered May 30 '19 at 01:19

Aaron Bertrand

180,303
28
400
614

Thank you for your answer. I had already decided to try a similar method of dropping the foreign key constraints and truncating the tables. It seems to have worked properly. Take a look and see if you see any issues in my answer. I appreciate your help! – Appleoddity May 30 '19 at 05:37
@Appleoddity well, removing keys and truncating is a lot more work, obviously, and there is a tiny bit more logging involved, and some more too with the FK changes, but whatever floats your boat I guess. – Aaron Bertrand May 30 '19 at 10:39

score 3 · Accepted Answer · edited Sep 07 '22 at 19:28

Building on @Aaron Bertrand's answer. I decided to delete the foreign key constraints and truncate the tables and then recreate the constraints.

This is the script I used to clear the e-mail history and it took about 1 second to run total:

BEGIN TRANSACTION;
USE MSDB;
ALTER TABLE [dbo].[sysmail_attachments] DROP [FK_sysmail_mailitems_mailitem_id];
TRUNCATE TABLE [dbo].[sysmail_attachments];
ALTER TABLE [dbo].[sysmail_send_retries] DROP [FK_mailitems_mailitem_id];
TRUNCATE TABLE [dbo].[sysmail_send_retries];
TRUNCATE TABLE [dbo].[sysmail_mailitems];
TRUNCATE TABLE [dbo].[sysmail_log];
ALTER TABLE [dbo].[sysmail_attachments]  WITH CHECK ADD  CONSTRAINT [FK_sysmail_mailitems_mailitem_id] FOREIGN KEY([mailitem_id])
REFERENCES [dbo].[sysmail_mailitems] ([mailitem_id])
ON DELETE CASCADE
ALTER TABLE [dbo].[sysmail_attachments] CHECK CONSTRAINT [FK_sysmail_mailitems_mailitem_id]
ALTER TABLE [dbo].[sysmail_send_retries]  WITH CHECK ADD  CONSTRAINT [FK_mailitems_mailitem_id] FOREIGN KEY([mailitem_id])
REFERENCES [dbo].[sysmail_mailitems] ([mailitem_id])
ON DELETE CASCADE
ALTER TABLE [dbo].[sysmail_send_retries] CHECK CONSTRAINT [FK_mailitems_mailitem_id]
COMMIT;

After that, it was just a matter or shrinking (and setting the initial size of) the msdb and tempdb databases.

If @Aaron Bertrand's answer is right for you, please mark it as the Accepted Solution. — CLS, Nov 03 '20 at 12:40

How to delete SQL Server Database Mail history WITHOUT stored procedures

2 Answers2