SQL Server Central
We recently had an issue where MSDB was growing very quickly, it turned out it was due to a huge increase in data we was directly emailing to users.
We now run a weekly job just to maintain the MSDB email tables as whole copies of emails, attachments and logs are kept. I first run a stored procedure designed to maintain the tables and then directly delete from the tables (I would expect these to delete nothing).
use msdb go declare @DeleteToDate datetime --I have set this to keep 60 days set @DeleteToDate = DATEADD(d, -60, getdate()) --Print the date for logs print @DeleteToDate print 'Using inbuilt script to maintain tables' EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteToDate --Line below is SQL Server 2008 only --EXECUTE msdb.dbo.Sysmail_delete_log_sp @logged_before = @DeleteToDate --maintain sysmail_allitems print 'maintain sysmail_allitems' DELETE FROM msdb.dbo.sysmail_allitems where sent_date < @DeleteToDate --maintain log file print 'maintain sysmail_log' DELETE FROM msdb.dbo.sysmail_log where log_date < @DeleteToDate --remove old emails print 'maintain sysmail_mailitems' delete from msdb.dbo.sysmail_mailitems where sent_date < @DeleteToDate --remove retries (Hopefully this shouldn;'t be to high a number) print 'maintain sysmail_send_retries' delete from msdb.dbo.sysmail_send_retries where last_send_attempt_date < @DeleteToDate --remove attachments (If you are removing the email no point in keeping the attachments) print 'maintain sysmail_attachments' DELETE FROM dbo.sysmail_attachments WHERE last_mod_date < @DeleteToDate