Depending on business requirements, there can be a use case where significant number of Email reports need to be sent that have large report attachment files.

Over time, all those attachments will use up a lot of storage space in the database.

Here is automated solution that be implemented to flag some of those attachments for deletion without deleting attachments directly.

Please note that the suggestion proposed in this article adds on to the out of the box features documented below.

Data archiving / Email Retention plugin: Data archiving

Email retention: Email retention

It is possible to delete Email records and their file attachments will cascade delete.

Here are some sample basic queries to get started with like:

target_table=NULL^typeINsend-failed,send-ignored,sent
OR
target_table=sysauto_report^typeINsend-failed,send-ignored,sent

Additional filter criteria should be used to avoid deleting wrong records such as :

• date time interval
• subject filter
• keyword filter 

Here is a sample script that may be run as part of an automated scheduled job.

Please note that this is just an example, and it should be further extended and tested before, never run this in production without testing.

It would be worthwhile considering to add auditing so that records would only be flagged for deletion and their sys_id added to a temporary table and reviewed by and administrator before finally deleting them.

var gr = new GlideRecord('sys_email');
//No target table
gr.addEncodedQuery('target_table=NULL^typeINsend-failed,send-ignored,sent');
// sys_report table
// gr.addEncodedQuery('target_table=sysauto_report^typeINsend-failed,send-ignored,sent');

gr.query();
// Test row count, rowcount can impact performane if there are many.
gs.log(gr.getRowCount());
// Add conditions or auditing code here before deleting.
// Once you are sure, then delete with that code.
gr.deleteMultiple();