HEELPBOOK - 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!



############ ARTICLE INFO #############

Article Month: June
Article Date: 25/06/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-purging-database-mail-history/
Source: http://sqlandme.com/2012/05/10/sql-server-purging-database-mail-history/
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook