Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Purging Database Mail data from msdb

On Sunday morning, I got paged by one of our monitoring systems that we were low on free disk space on one of our production servers.  The specific resource that was low on free disk space was our "Backup" mount point.  Since I didn't think this should be the case for a few more months, I looked into each folder to see if any database backup was larger than it should be.  That's when I noticed that our msdb backups were 6 gigabytes in size. 

Typically the msdb database is larger than it should be due to backup history, but we already have a job to purge that data.  The job was successful the last time it ran, so I decided to find out if there were any other tables that had an unusual amount of data in them.  SQL Server 2005 makes this easy for us by providing the "Disk Usage by Top Tables" report in Management Studio.  When I viewed this report, I noticed that the sysmail_attachments system table was at the very top.  It showed that the table was consuming 6 gigabytes of space.

The sysmail_attachments system table is used by Database Mail to store the email attachments.  We have a job that runs hourly that sends an email with an attachment to several people.  The table only had a few thousand rows in it, but it took several minutes to delete everything that was older than a couple of days.  While the delete was running, I checked Books Online to see if there was a system stored procedure to do the purge or if I was going to need to write my own.  I found sysmail_delete_mailitems_sp. 

If you send attachments via Database Mail, I would recommend that you create a job that runs sysmail_delete_mailitems_sp on a scheduled basis, perhaps daily or weekly.  This recommendation can also be found in Books Online in the "Remarks" section of the "sysmail_delete_mailitems_sp" topic.

Here is an example of what to put in the job step:

DECLARE @d datetime

SET @d = DATEADD(dd, -5, GETDATE())

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d

Print | posted on Monday, April 28, 2008 4:04 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# Great find: I NEEDED to purge Database Mail but didn't know it

I checked out the count in my own msdb and was surprised buy the volume - I've created the job you suggested and feel good that this is being managed now. THANK YOU for sharing!!!
5/5/2008 12:17 PM | Normajean
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET