Are you using database mail to send SQL Agent job completion messages, and alerts? If so, are you also purging the old mail items stored in msdb? In this tip, I will show you how to purge database mail.
Database mail is stored in the msdb database. If you don’t purge it periodically your mail items will take up more and more space in the msdb database. How big is your msdb database, right now? If you are sending lots for database mail without purging your database mail periodically then very possibly your database mail might be taking up lots space.
There is a system stored procedure that you can use to delete the mail items stored in the msdb database. It is called sysmail_delete_mailitems_sp. This stored procedure can be used to delete your mail items by date, and/or mail sent status. There are four different mail send status values: sent, unsent, retrying and failed. Below you will find a couple of examples on how to use this stored procedure.
Suppose at your shop you have decided that you want to retain the last 30 days of database mail. To deleted all the database mail that is older than 30 days you can run the following code:
DECLARE @PurgeDate datetime = dateadd(dd,-30,getdate()); 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”:
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_status='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.
