TrebuchetMail is far and away the largest table in our database - coming in at around 36GB.
I'm exploring ways of removing rows related to closed Incidents but I have to imagine the vast majority of that 36GB is wrapped up in the EmailDetails field.
Does anyone know if that field is ever actually used anywhere? It looks like the same data is in the Journal-Mail History record so I'm not sure why it's getting stored.
This is related more to the internal functioning of the application but I think that since TrebuchetMail table stores the MCID (tag generally present at bottom of Cherwell generated email), this is needed for automation email monitor to link the incoming emails to the ticket. We do not have Journal-Mail History table (just the Journal table). I think you can insert a custom value(like Redacted) in the EmailDetails field for all the tickets older than 6 months or so. This is the safest way.
The only place you'll notice this being used, is when you are looking at a ticket, on the journals tab, the 'E-mail' button that renders the email if clicked from the journals toolbar.
This renders an HTML copy of the email (with attachments) from the TrebuchetMail table.
Also used for email monitoring matching.
It requires a pretty nifty SQL query to match this up to Incidents;
If you need some help with it, feel free to reach out https://defeditsoftware.com
Ahh! I'm glad to know why they actually keep that data around.
The fact that the browser client completely lacks the 'Email' button (and we've switched to a tab setup that doesn't have it in some cases) makes me much more comfortable clearing out that field.
Regarding CMI tags and threading, it was my assumption that completely clearing out the TrebuchetMail records for a given Incident would prevent any email update from updating back to a ticket. However, I was pleasantly surprised that the Email Monitor seems to go a pretty good job of finding Incident ID's in the subject line as a backup. (Not that we want to remove rows from this table on active tickets, but just in case we messed things up).
I don't think the SQL is that complicated ...
from TrebuchetMail TM
left join Journal J
on J.EmailID = TM.MailMsgID
left join IncidentSR ISR
on J.ParentRecID = ISR.RecID
where ISR.RecID in
Yes the email monitor has an option to identify incidents by ID - This works very well;
There are just a few cases where that could break, and typically only if you embed the short description of a ticket in the subject line of outgoing emails (Imagine someone creates a new ticket by replying to an old, closed ticket's resolution email, and the subject on the new ticket is 'RE: Incident XXXX Has Been Resolved', then the new ticket emails out 'Incident yyyy Created - 'RE: Incident XXXX Has Been Resolved'')
Also - It's great that you're familiar with SQL, there are tons of Cherwell administrators out there that have literally never worked with it
To give an update on this, after NULL'ing out the EmailDetails field, our TrebuchetMail table went from 36GB to 1GB.
We have specifically turned off/worked around the 'Email' button that appears for Mail History records in the Journal tab, so we don't really use that data; it might not be the case for everyone though!
For reference, this is the SQL we used to touch the table without (hopefully!) clobbering the transaction log. We ship our log every 15 minutes, so I just waited about 20 minutes each run.
set EmailDetails = null
where RecID in (
select top(100000) recID
where EmailDetails is not null
After some more testing, I believe we'll move this to a scheduled script on the database server.