Home > Catalog Views, Database Mail, SQLServer > SQL Server – Purging Database Mail History

SQL Server – Purging Database Mail History

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.

To check all mails processed by Database Mail, you can use sysmail_allitems catalog view:

SELECT      COUNT(*)

FROM        msdb.dbo.sysmail_allitems

Output:

———–

125

There are 3 siblings of this catalog view sysmail_faileditems, sysmail_unsentitems and sysmail_sentitems which shows mails of different status respectively.

If you are frequently sending larger attachments using database mail this can cause msdb to grow rapidly. All attachments stored in msdb database are available in sysmail_attachments.

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 = '2012-05-10 00:00:00',

      @sent_status = 'sent'

Output:

(100 row(s) affected)

You can query the sysmail_event_log view to check the deletions that has been initiated.

SELECT      description

FROM        sysmail_event_log

ORDER BY    log_date DESC

Output:

description

Mail items deletion is initiated by user “sa”. 100 items deleted.

DatabaseMail process is started

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. veronelazio
    May 10, 2012 at 8:26 pm

    Thanks so much, very interesting post, something that I rarely look at if ever that is now back on my radar!

  2. veronelazio
    May 10, 2012 at 8:28 pm

    Thank you for this very interesting post! This log has been somewhat off of my radar until today!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 264 other followers

%d bloggers like this: