Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field
| The issue described in this content, has been fixed by ServiceNow in the Vancouver family release. |
Hi all,
Any Predictive Intelligence solutions active in your instance? You might want to check your Attachment Document table [sys_attachment_doc] on orphaned records with sys_created_by "sharedservice.worker". Depending on the size of your instance and how long you are running Predictive Intelligence already, you might have hundreds of thousands of orphaned Attachment Document records or even more in your instance.
Oke, perhaps I am informing you all a bit too fast, PRB1727176 by ServiceNow did not yet confirm this as a bug, though I know it is, the PRB is just not being handled that quickly 😀.
You might ask yourself, "Didn't I read this already before?" Similar could be yes, about 8 months ago I did report a bug on an out-of-the-box Table Cleaner which resulted in orphaned Attachment records [sys_attachment] concerning Predictive Intelligence. This time it's a different Table Cleaner which results in orphaned Attachment Document records.
Orphaned Attachment Documents
Investigating the rapid growth of the Attachment Document table for a customer, I noticed more than 300 thousand orphaned Attachment Document records and daily increasing. Manually filtering the Attachment Document table I could see these numbers, though details are not shown due to out-of-the-box Access Controls ("Number of rows removed from this list by Security constraints").
The filter that you can use to check any orphaned Attachment Document records:
sys_attachmentISNOTEMPTY^sys_attachment.sys_idISEMPTY
For the customer this was at, I could only verify this in Production and where changing the Access Controls was a huge no no. Luckily, using background script works perfectly fine to see more details, which also clearly showed sys_created_by "sharedservice.worker". Hé, isn't that Predictive Intelligence? Yes it is!
Table cleaner
Since I know from experience that Predictive Intelligence Attachment records are maintained through Table cleaners, I immediately investigated these. One Table cleaner specifically raised some doubts:
The doubt I had, the "Cascade delete" field unchecked. Wouldn't this actually cause orphaned records? Yes it does.
Cascade delete
After updating the Table cleaner, checking the Cascade delete field, and removing the orphaned Attachment Document records with sys_created_by "sharedservice.worker", ServiceNow Support rebuild the Attachment Document table. Monitoring the table size in the days after confirmed that the Attachment Document table was not growing that rapidly anymore. Also manually verifying the number of orphaned Attachment Document records, did not show any increase anymore.
ServiceNow Support
In contact with ServiceNow Support through a case, they did raise PRB1727176 to investigate this. The PRB is still open, though honestly we already know that this is an out-of-the-box bug.
Example cleanup script
Below is an example cleanup script that you could use for the orphaned Attachment Document records. Obviously, test carefully first on one or more sub-production instances.
(function() {
var grAttachmentDocument = new GlideRecord('sys_attachment_doc');
grAttachmentDocument.addEncodedQuery('sys_attachmentISNOTEMPTY^sys_attachment.sys_idISEMPTY^sys_created_by=sharedservice.worker');
grAttachment.deleteMultiple();
})();
Rebuild Attachment Document Table
As mentioned already, ServiceNow Support was involved to rebuild the Attachment Document table after the orphaned records were cleaned up. To have a table rebuild, you can simply raise a ServiceNow Support case. ServiceNow Support will optimize the table from the backend, and would likely opt to actually rebuild the table. Having the table optimized will 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? |
Kind regards,
Mark Roethof
Independent ServiceNow Consultant
4x ServiceNow Developer MVP
4x ServiceNow Community MVP
---
