Sunday, September 2, 2012

MSDB tip, clean up your mail

Just a quick tip since I encountered this recently.  Came across a MSDB database in my journey that was exceptionally large, did some delving and found that they were using database mail to send attachments frequently and no one ever bothered to clean up the history.

By default, MSDB will store all those attachments you send, so you should clean them up or it will grow... well forever.  So Microsoft provides a system stored procedure that allows you to purge ones older than a specified date.  So for example if you wanted to purge everything older than 30 days, you could do something like this:

 declare @purgeDate datetime  
 SET @purgeDate = dateadd(dd, -30, getdate())  
 EXECUTE msdb.dbo.sysmail_delete_mailitems_sp   
   @sent_before = @purgeDate;  

No comments:

Post a Comment