January 14, 2013 at 3:05 am
Can these 2 tables in the msdb database, simply be truncated.They have grown quite large and simply need some sort of management.
dbo.sysmail_mailitems
dbo.sysmail_log
January 14, 2013 at 3:14 am
Use msdb.dbo.sysmail_delete_log_sp and msdb.dbo.sysmail_delete_mailitems_sp to delete from the two tables.
January 14, 2013 at 3:51 am
I have been using msdb.dbo.sysmail_delete_mailitems_sp
Wasn't aware of the other one. Will do. Thanks
January 15, 2013 at 5:25 am
I have a job that runs monthly that runs this:
DECLARE @DeleteBeforeDate DateTime = (Select DATEADD(d,-30, GETDATE()))
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate
January 15, 2013 at 6:15 am
Before you start whacking your outgoing email history, you might want to ask yourself if you're going to need it. I'm not saying you do, but rather that you should at least consider the question. It's a necessity for what we do and might not apply to you.
BTW, there's one more table in the equation: msdb.dbo.sysmail_attachments
We had the same tables get pretty big. We decided to keep a certain number of months of data online and archive the rest. We archive the email data into new tables in another database, archive the basic mail configuration (accounts, profiles and servers) and then do the purge. The database with the archive is then taken offline. So, we get the space back is msdb but can still get to our email history if necessary.
The matter of waiting for the ghost record cleanup process to run across the deleted rows is another part of the story. Reorganizing the index helps for the main partition, but the LOB data is another matter I don't fully get yet.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply