Orphaned ML Update Set attachments: Fixed (be aware though... the fix does not fix everything!)

Mark Roethof · ‎10-10-2023

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

Hi there,

The root cause of orphaned ML Update Set [ml_update_set] attachments (PRB1666307) has been fixed with the ServiceNow Vancouver release. Be aware: this fixes new occurrences of orphaned attachments, though already existing ones you need to clean up yourself!

Orphaned attachments

Investigating the Attachment [sys_attachment] table for a customer, it showed more than 900 thousand records for table name "ml_update_set". That was more than 26 percent of all records in the Attachment table for that customer, a very high odd percentage! Looking into the ML Update Set table itself, the table only contained a bit over 10 thousand records. So 10 thousand records, and 900 thousand attachments?

The ML Update Set table is maintained by a Table Cleaner, which has the age set to 7 days (or actually 604,800 seconds). Looking at the ML Update Set records the Table Cleaner does maintain the ML Update Set records, though also causes the orphaned attachments for table name ml_update_set. All attachments for table name ml_update_set older than 7 days appeared to be orphaned.

A few months ago, I also wrote an article on this subject:
Any Predictive Intelligence solutions in use on your instance? Check your Attachments table ASAP!

Fixed in Vancouver

After creating a ServiceNow Support Case, ServiceNow confirmed that the behavior seen is an issue and created a Problem (PRB1666307). After a bit over a month, the Problem was placed in state Fixed: it's fixed in the Vancouver release 😀 The fix is also mentioned in the release notes of the Vancouver release: "The table cleaner on [ml_update_set] deletes records older than 7 days, but leaves orphaned records in [sys_attachment]". For all new ML Update Set attachments, there should not be new occurrences anymore regarding the orphaned ML Update Set attachments.

Existing orphaned attachments

While new orphaned attachments regarding ML Update Set should not occur anymore, already existing orphaned attachments will still be on your instance. Already existing orphaned ML Update Set attachments won't be cleaned up for you. Cleaning up orphaned ML Update Set attachments is a one-time task that you do need to take care of yourself. While for some customers this might only involve a handful of records, though there will be customers with hundreds of thousands or even more orphaned ML Update Set attachments. This will have an effect on performance, though also for example the size of your Database Footprint (the example mentioned of more than 900 thousand orphaned ML Update Set attachments was good for way over 100 gigabytes of Database Footprint).

Example cleanup script

Below is an example cleanup script that you could use. Obviously, test carefully first on one or more sub-production instances.

(function() {

    var table_name = 'ml_update_set';

    var grAttachment = new GlideRecord('sys_attachment');
    grAttachment.addEncodedQuery('table_name=' + table_name + '^table_sys_id!=');
    grAttachment._query();

    while(grAttachment._next()) {
        var grRecord = new GlideRecord(grAttachment.table_name);
        grRecord.addQuery('sys_id=' + grAttachment.table_sys_id);
        grRecord.setLimit(1);
        grRecord.query();

        if(!grRecord.next()) {
           grAttachment.deleteRecord());
        }
    }

})();

Rebuild Attachment Document Table

After having the orphaned ML Update Set attachments cleaned up, you do need to have the Attachment Document table optimized. ServiceNow Support can do this for you through a Support Case. They will optimize the table from the backend, and would likely opt to actually rebuild the table. Having the table optimized would improve performance on the Attachment Document table and lower the Database Footprint size.

---

And that's it. Hope this benefits you in getting your instance a bit healthier. If any questions or remarks, let me know!

C	If this content helped you, I would appreciate it if you hit bookmark or mark it as helpful. Interested in more Articles, Blogs, Videos, Podcasts, Share projects I shared/participated in? - Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field

Kind regards,

Mark Roethof

Independent ServiceNow Consultant
4x ServiceNow Developer MVP

4x ServiceNow Community MVP

---