SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trunacte MSDB tables.


Trunacte MSDB tables.

Author
Message
emile.milne
emile.milne
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 224
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
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25354 Visits: 6519
Use msdb.dbo.sysmail_delete_log_sp and msdb.dbo.sysmail_delete_mailitems_sp to delete from the two tables.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


emile.milne
emile.milne
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 224
I have been using msdb.dbo.sysmail_delete_mailitems_sp
Wasn't aware of the other one. Will do. Thanks
Summer90
Summer90
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7395 Visits: 3831
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
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50907 Visits: 10844
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search