Cleaning Up SQL Database Mail Logging Records

This morning in my production cluster I noticed MSDB had grown to almost 10gb.  After some help from my friends at #SQLHelp, I ran one of the built-in reports, which are great…thank you Microsoft, ‘Disk Usage by Top Tables’ to tell me which table(s) had grown since I last checked this system database.  I think I found a winner: sysmail_mailitems had an outrageously high amount of records.  Why aren’t these being cleaned up in a job similar to sp_delete_backuphistory or sp_purge_jobhistory?

In order to clean these items up, use the following code:

USE MSDB

EXEC sysmail_delete_log_sp @logged_before=’2012-01-13 12:00:00′

You could script this out to run nightly or weekly and remove the last 30 days.  Enjoy!

sysmail_mailitems

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 13, 2012, in Scripting and tagged , , , . Bookmark the permalink. Leave a comment.

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

%d bloggers like this: