How to purge Microsoft SQL email Items

How to purge Microsoft SQL email Items

There are times that housecleaning older mail in your database needs to be done.  A simple scheduled task can be done every month to purge what is no longer necessary.  SQL Server stores all mails and attachments in msdb database. To avoid unnecessary growth of msdb database you should remove these mail history unless it is required for auditing or other purposes.

As always make sure you have your backups done prior to running any sort of removal in the event you did something incorrectly.  By purging you are reducing the amount of wasted space in the msdb database that could be used for other things activities.

Simply running the following statement using T-SQL you can check the amount of database mails that have been processed from the catalog msdb.dbo.sysmail_allitems.   There are 3 additional  views which shows mails in their different statuses:  sysmail_faileditems, sysmail_unsentitems and sysmail_sentitems.

 

SELECT  COUNT(*)  FROM  msdb.dbo.sysmail_allitems

You should get back the count of how many mail items are stored in the msdb.

How to delete mail

To delete mail items you can use system stored procedure sysmail_delete_mailitems_sp, it has below syntax:

sysmail_delete_mailitems_sp [@sent_before] [@sent_status]

You can delete mail using either of the parameters, @sent_before deletes all mail that were sent before specified date, and @sent_status deletes all mails with specified status.

For example, to delete all mails which are sent and are older than current month we can use:

EXEC  msdb.dbo.sysmail_delete_mailitems_sp

@sent_before = ‘2017-05-10 00:00:00’,

@sent_status = ‘sent’

You can modify the @sent_before to do the following:

DECLARE @PurgeDate datetime = dateadd(dd,-30,getdate());  

@PurgeDate will be the date from 30 days or older which is a good idea to remove that or you can change it to
-10 for anything older than 10 days.

So the statement would be

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

You may choose to only delete mail items that have been successfully sent.  If that is the case then you can run the following code to delete your database mail based on the sent­_status of “sent”.

If you are not periodically deleting your mail then you might want to consider cleaning up old database mail that no longer has value.  Determine what retention period you should use for your database mail.  With your established retention period then just set up a SQL Agent job to purge your database mail based on your retention period.