
- Post History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
02-16-2024 02:44 AM - edited 07-27-2024 11:10 AM
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!
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
9x ServiceNow MVP
---
- 4,848 Views
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
A word of warning for anyone thinking of enabling the Purge Orphan Attachments/sys_attachment_soft_deleted functionality added in Washington, if your attachment table is large (and if you're worried about attachment table size/performance it probably is...), then I doubt the current design will be able to successfully run.
The problem is the 'purgeAttachments' function that's supposed to find the orphaned attachments. Depending on whether the 'glide.attachment.orphan.clean_old_orphan_records' property is true or false it's either querying the sys_attachment table by sys_created_on (then ordering it), or worse trying to order the entire table by sys_created_on. There's no index on the sys_attachment table for sys_created_on, so the SQL query that generates is more likely than not to just outright fail to return results due to a timeout if you've got millions of attachments in the system.
This could be fixed by ordering by the sys_id (and changing the saved checkpoint to by a sys_id rather than date-time), and moving the 'table name does not contain ZZ_YY/invisible' logic to the script rather than the query, though obviously customise/use undocumented features at your own risk.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Nice article!
Me, as many others are continuosly looking for strategies related to attachment management, and this is a good one.
Just one thing that would make it better, it terms of performance, that is, for the first code snippet, to use GlideAggregate, and groupBy .table_sys_id to scan faster the different tables.
A second idea, is that given that .table_sys_id first character is hexadecimal, then, to scan those starting by a different character every day, this will need 16 days to scan the full instance, but the script to find the attachments will not take so much time, and will reduce the impact on database performance.
Thanks for opening this thread
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
wondering what went wrong... 🙂
Issue with archive table clean-up using PurgeOrphanAttachments job - Known Error
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
var PurgeOrphanAttachments = Class.create();
PurgeOrphanAttachments.prototype = {
purgeAttachments: function () {
return;
},
type: 'PurgeOrphanAttachments'
};
That is all i see on the script include. Is this normal?