Hi Paul,
Sure I can!
So, every attachment is connected to a specific record based on the table name and table sys id fields.
To ease my explanation, let's consider an incident as the main record.
If you delete an incident the cascade delete rules will delete the sys_attachment automatically. But if you are doing the deletion with table cleaners or using the archive engine, you'll need to do that deletion by yourself.
Since there is no reference in the attachment to the original record (as I mentioned before, the linkage is done via table name and table sys id) you need to query the system to check if the record associated with the attachment still exists. You can use a code similar to this one:
checkOrphanSA();
function checkOrphanSA(){
var gr = new GlideRecord("sys_attachment");
gr.addEncodedQuery(<filter query to pick the attachments you want to analyze");
gr.setLimit(1000);
gr.query();
while(gr.next()){
var gr2 = new GlideRecod(gr.table_name);
gr2.get(gr.table_sys_id);
if(!gr2.isValidRecord()){
//this means that the parent record does not exist! The sys_Attachment should be deleted!
var gr3 = new GlideRecord("sys_attachment_doc");
gr3.addQuery("sys_attachment", gr.sys_id);
gr3.query();
while(gr3.next()){
gr3.deleteRecord(); //delete sys_attachment_doc recird
}
gr2.deleteRecord(); //deletes orphan sys_attachment record
}
}
}
You should do this in chunks of 1000 records in order for you to control the timings.
Then you should do the same for sys_attachment_doc.
In specific conditions, when you delete the sys_attachment the sys_attachment_doc records continue to exist.
You can use this script for that:
cleanAttachments();
function cleanAttachments(){
var gr = new GlideRecord("sys_attachment_doc");
gr.addEncodedQuery("sys_attachment.table_nameISEMPTY");
//gr.setLimit(1000);
gr.query();
while(gr.next()){
gr.deleteRecord();
}
}
Use this in fix scripts.
Test out the first script and let me know the result.
Hope this helps!
Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!
Best Regards,
Filipe Cruz
