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