Attachment Documents removals when sys_attachment is deleted

[AnttiTapiola]

Hello Experts,

could someone bring more light how ServiceNow out of box attachment deletion works. If as example emails are archived and terminated, corresponding sys_attachment record is deleted by system. It seems that ServiceNow still keeps Attachment document records having actual document payload. Such records has sys_attachment reference as empty.

Could we say as rule that Attachment Document records having empty sys_attachment references are not useful and could be removed by some automated process?

[Bert_c1]

Hi,

I can provide some information from Tables & Columns perspective.  The sys_attachment_doc table has a field that links the records there to a sys_attachment record.  that field is named 'sys_attachment' it is a Reference field and has Reference Cascade Rule set to "Delete". OOB  That means when a sys_attachment record is deleted, any related records in sys_attachement_doc should also be deleted.

See: Cascade Delete Rules

So it seems that if a record in sys_attachment is deleted, any related records in sys_attachment_doc are also deleted. If I understand your post correctly, you seem to have a problem that should be reviewed by Servicenow Support. Check the value of the "Reference cascade rule" on the 'sys_attachment' field in the 'sys_attachment_doc' table. It sounds like the Reference cascade rule is set to "Clear" and not "Delete".

You could write a scheduled job to go through sys_attachment_doc records, and check is a record exists in sys_attachment with a sys_id value from the 'sys_attachment' field. If no record exists in sys_attachment table, delete the sys_attachment_doc record.  Or delete records from sys_attachment_doc where the 'sys_attachment' field is empty. But again, you shouldn't have to do that.

[AnttiTapiola]

Thanks for your comments. I figured out that by manually deleting sys_email record both sys_attachment and corresponding sys_attachment_doc records are deleted. The problem seems to be in ServiceNow out of box archiving functionality which leaves sys_attachment_doc records there.

Tried as well Archive Destroy rule with delete related record but it didn't impact the case. It looks like easiest way is not to use ServiceNow archiving but implementing scheduler to remove emails (and thus attachments and related documents).