Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field
Hi there,
When having Predictive Intelligence Solution Definitions actively in use on your instance, you might want to closely read this article. Or perhaps if you are following my Instance Scan posts, you are already aware of what I'm going to share 🙂 Just a piece of knowledge gained out there in the field.
Attachments
While analyzing the Attachments table for a customer, by Instance Scan findings and manually running through the table, I noticed a huge amount of Attachments with an invalid table_sys_id. The majority of these records were specifically for records with table_name "ml_update_set". Even more specific: from the 900 thousand attachments with table_name ml_update_set, more than 98% concerned records where the table_sys_id mentioned did not exist at all. If those records are truly floating, don't have a valid parent record, and are never going to be used... clean it up 🙂 For the customer these records concerned about 26% of all Attachments and more than 100GB of Database Footprint!
Predictive Intelligence Solution Definitions
When activating and training Predictive Intelligence Solutions Definitions, amongst others this will generate records in the ML Update Set table and a record in the Attachments table that has the table_name set to ml_update_set. While the ML Update Set table out-of-the-box is maintained by a Table Cleaner (604,800 seconds in age, matching the sys_created_on field), the Attachments that have the table_name set to ml_update_set are not maintained.
ServiceNow Support
While I only have one customer with Predictive Intelligence in use, I did manage to kinda force the same behavior on a Personal Developer Instance. To verify if this is indeed out-of-the-box behavior (or that something is broken on the customer's instance), I created a ServiceNow Support Case. Confirmation from ServiceNow Support would be a good thing, and perhaps it is already known and a solution or workaround available, or when not known ServiceNow can benefit from this. After a few days, ServiceNow Support confirmed what I described as an issue and they've opened PRB1666307 to do more investigation. Hopefully this will lead to being solved in a future hotfix/patch/upgrade. Currently the PRB is still in state New.
Table Cleaner
While the issue is a severe one, as mentioned 26% of all Attachments and more than 100GB of Database Footprint for this customer, I decided to look into a (temporary) workaround for the invalid ml_update_set Attachments records. Obviously we want to get rid of these Attachments, though at the same time wanting to automatically remove future ones. And not forgetting, since it's a massive chuck of GB, optimizing the Attachments table.
There are multiple approaches thinkable for this issue. My preference goes for using a Table Cleaner [sys_auto_flush] for such. Basically create a new Table Cleaner record for table "sys_attachment", set the "Matchfield" and "Age in seconds" to be equal to the out-of-the-box Table Cleaner for the ML Update Set table, and add as condition table_name=ml_update_set.
If you are not familiar yet with Table Cleaners, do visit the ServiceNow Product Documentation on this.
Result
Depending on your instance size and how long the Predictive Intelligence Solution Definitions were already running on your instance, this might concern just a few Attachments records or perhaps even millions of Attachments records. Depending of the size, it can be that Table Cleaner won't be able to cleanup all the Attachments records in one or two runs though it will catch up quickly!
---
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? |
Kind regards,
Mark Roethof
Independent ServiceNow Consultant
4x ServiceNow Developer MVP
4x ServiceNow Community MVP
---
