Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trunacte MSDB tables. Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 3:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, 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
Post #1406624
Posted Monday, January 14, 2013 3:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
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
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

Post #1406632
Posted Monday, January 14, 2013 3:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
I have been using msdb.dbo.sysmail_delete_mailitems_sp
Wasn't aware of the other one. Will do. Thanks
Post #1406644
Posted Tuesday, January 15, 2013 5:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:08 PM
Points: 1,308, Visits: 2,888
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



Post #1407193
Posted Tuesday, January 15, 2013 6:15 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 4,442, Visits: 3,884
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
Post #1407220
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse