CJSmith dot me

I dump stuff I find useful here

Maintain MSDB email tables

Published / by Chris Smith / Leave a Comment

Credits:
SQL Server Central
SQL Authority
Microsoft Docs
Microsoft Docs

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

Leave a Reply