What is the purpose of the sys_email_log table

[Tom Siegel]

We are headed down the path of purging legacy data from our instances to better manage our database size. I am currently working on cleaning up the email tables, one of which is sys_email_log. This table shows up on our list of top 100 tables according to size. Can someone please tell me what the data on this table is used for? I can find the same information on the sys_email table although it does allow me to query for the event that created the email and the notification used, via the list view. My hope is to keep these records around for no more than a year in case I need to track emails sent using a specific event or notification, and then handle the purging of the sys_email and sys_email_attachment tables independently?

Any guidance would be appreciated.

[jMarshal]

You can get a lot of info from sys_email, but sys_email_log does contain some different info, too. It references sys_email and adds reference to the event and notification records that are used to generate the email in sys_email.

Essentially sys_email is where the actual email which was sent, is stored (recipient, date, body, etc) -- but sys_email_log tells you how/why it was sent, you can't really get that from sys_email by itself -- though, you can likely glean that info from examining sys_email along with your task tables (inc, sctask, etc.) that generate most of the emails.

sys_email, sys_email_log and sys_email_attachment all work in conjunction with each other and I would recommend (as a best practice) to archive/delete data in tandem (same retention policy applied to all 3 tables), when cleaning up your instance.

[Tom Siegel]

jMarshall Thanks for the reply. I think we are both on the same page in some respect...

I can get the event that created the email, the notication, created by, and a few other fields from the sys_email_logs, all searchable via the list view. The same information also exists on the sys_email record although I would have to open the individual sys_email record in the form view to get that info.

My current thought process though is that neither sys_email nor sys_email_attachment are dependent on sys_emai_log. The email, event, and notification fields on sys_email_logs are all reference fields (email->sys_email, event->sysevent, notification->sysevent_email_action). So if these sys_email_log records go away, they should in no way impact the records on sys_email and sys_email_attachment.

Any holes in my logic?

Thanks,

Tom

[jMarshal · Accepted solution]

Notice though, that the information you see (in form view) related to the event and notification themselves, when you open the record from sys_email.list...is a related list, meaning that removing the data in sys_email_log that it references, will "break" that data in the sys_email record you are looking at.

[Tom Siegel]

Ahh - I see. What you have highlighted is an embedded list on the sys_email form pointing to sys_email_log. So yes deleting the corresponding sys_email_log for that sys_email record would make all of the fields show as (empty). Event, notification, etc.

Well that changes things. Thanks so much for your insight!!