Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field

Hi there,

(Almost) every ServiceNow customer will have this issue on their instance: orphan Attachments. Attachments that got orphaned because of incorrect Table Cleaner configuration, because of Attachments added to a new record while the new record is abandoned, because of incorrect manual Data cleanups, etcetera. These orphan Attachments are useless, no one can use them, they eat up table space, they decrease performance on the table, they contribute to Database Footprint over licensing, etcetera.

For this particular issue, I created a custom clean-up for several customers to solve this issue since ServiceNow does not have anything in place for this. Or... with the Washington DC release there is! At least I think there is, since it's undocumented, that's what MVPs are for .

Orphan Attachments

Keeping control of orphan Attachments is a necessity in any ServiceNow instance. Unfortunately, there is no easy way to query orphan Attachment records. Orphan Attachments don't have a field to dotwalk to the source record, creating a Database view won't help, visually no fields on the Attachment record change when it becomes orphaned. Through a bit of scripting logic orphan Attachments can be identified. However, it can be heavy on the duration side when the Attachments table consists of tens of millions of records.

A scripted method I've used in the past to identify orphan Attachments:

(function() {

    var grAttachment = new GlideRecord('sys_attachment');
    grAttachment.addEncodedQuery('file_nameISNOTEMPTY^table_nameISNOTEMPTY^table_nameNOT LIKEZZ_YY^table_nameNOT LIKEinvisible.^sys_created_onRELATIVELT@hour@ago@1');
    grAttachment._query();

    while (grAttachment._next()) {
        if (gs.tableExists(grAttachment.table_name)) {
            var grSource = new GlideRecord(grAttachment.table_name);
            grSource.addEncodedQuery('sys_id=' + grAttachment.table_sys_id);
            grSource.setLimit(1);
            grSource._query();

            if (!grSource.hasNext()) {
        gs.error('# Orphan attachment: ' + grAttachment.getUniqueValue() + ' ('+ grAttachment.table_name + '.' + grAttachment.table_sys_id + ')');
            }
        } else {

        gs.error('# Orphan attachment: ' + grAttachment.getUniqueValue() + ' ('+ grAttachment.table_name + '.' + grAttachment.table_sys_id + ')');
        }
    }

})();

Personally, I wouldn't run such in a background script since it can run for a while. You might also limit the running by querying a specific table.

Custom Schedule

Most of the script above can also be used for a Scheduled cleanup, in a Scheduled Script Execution [sysauto_script] or Schedule [sys_trigger]. Below is a method I've used with several customers to clean-up orphan Attachments with a daily Scheduled Script Execution, limiting the deletions to 1,000 every run. It can definitely be optimized, or must if your instance contains multiple tens of millions of Attachment records.

(function() {

    var deletion_limit = gs.getProperty('glide.attachment.orphan.deletion_count_limit', 1000);

    var grAttachment = new GlideRecord('sys_attachment');
    grAttachment.addEncodedQuery('file_nameISNOTEMPTY^table_nameISNOTEMPTY^table_nameNOT LIKEZZ_YY^table_nameNOT LIKEinvisible.^sys_created_onRELATIVELT@hour@ago@1');
    grAttachment._query();

    var count = 0;
    while (grAttachment._next() && count < deletion_limit) {
        if (gs.tableExists(grAttachment.table_name)) {
            var grSource = new GlideRecord(grAttachment.table_name);
            grSource.addEncodedQuery('sys_id=' + grAttachment.table_sys_id);
            grSource.setLimit(1);
            grSource._query();

            if (!grSource.hasNext()) {
        grAttachment.deleteRecord();

        count++;
            }
        } else {
        grAttachment.deleteRecord();

        count++;
        }
    }

    if (count > 0) {
        gs.error('Maintain Orphaned Attachments: ' + count + ' attachments deleted.');
    }

})();

Washington DC release

When a new ServiceNow releases gets available, I like to turn it inside out as one of the first worldwide and search for the undocumented. One of the undocumented features added with the Washington DC release: "Purge Orphan Attachments". Purge Orphan Attachments which is a periodically triggered Scheduled Script Execution.

Looking into the script (through Script Include "PurgeOrphanAttachments"), it looks like an interesting setup. Similarly to my custom solution, querying all Attachment records and verifying one by one if its source record is valid. The script does have several extra checks and System Properties built in. A big difference though: the deletion mechanism is staged. Any orphan Attachment, will generate a record in the new "Delete Marked Attachments" [sys_attachment_soft_deleted] table and updates the "table_name" field and "table_sys_id" field to make these empty on the Attachment record.

The actual orphan Attachment clean-up is triggered by Scheduled Script Execution "Delete Marked Attachments" which runs every 2 days. This Scheduled Script Execution (through Script Include "DeleteMarkedAttachments") will query all Deleted Marked Attachments records and will delete the Attachment records associated with them.

Both Scheduled Script Executions are active out-of-the-box, though won't actually delete any orphan Attachments. Reason for this is that two of the System Properties involved don't exist on an out-of-the-box instance, and are defaulted to false. It concerns System Properties "glide.attachment.orphan.enable_cleanup" and "glide.attachment.orphan.clean_old_orphan_records". When adding these System Properties with value "true", the undocumented Purge Orphan Attachments starts working!

Note: Since this is undocumented and does not instantly work out-of-the-box, I cannot tell if this is yet supported.

Optimize table

After having the Attachment table [sys_attachment] - and with that the Attachment Docs table [sys_attachment_doc] - cleaned up, you do need to have at least the Attachment Docs table optimized. This will increase the performance of the table and will reclaim unused space on your Database Footprint. While you can optimize tables yourself, for larger tables it is saver to involve ServiceNow Support. Optimizing large tables yourself can take several hours and can lock the specific table. ServiceNow Support can do this from the backend, and would likely opt to rebuild the tables as that would avoid locking them.

Result

The result of maintaining orphan Attachments will differ for every ServiceNow instance. However on older ServiceNow instances, it's not uncommon to lower the table size of the Attachment Docs table by 25 percent or even more.

---

That's it. Hope you like it. If any questions or remarks, let me know!

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

9x ServiceNow MVP

---