Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field
Hi there,
Keeping your instance database footprint tidy? Reducing instance database footprint? An important subject, though actually a subject that barely gets attention. Barely if you search for any content, hardly any replies when reaching out on ServiceNow Community/LinkedIn/Slack/etcetera, and to be honest: also if you look at customers in general... so much focus on Development, so little (or almost no) focus on Platform or System Administration. Also there is not a single ServiceNow course that goes in depth on this subject.
So why is this such an important subject? ServiceNow runs in the cloud, right? Sure (unless you are on-prem of course), though that doesn't mean ServiceNow does everything for you! Keeping your instance database footprint tidy and reducing the database footprint, will have several positive effects: Performance gain, slimmer and optimized tables, less future manageability or technical depth, shorter system clone time, avoiding licensing implications if your database footprint gets over 4 TB, etcetera.
In the upcoming weeks, I will share several blogs regarding reducing your instance database footprint and keeping your instance database footprint tidy. All based on experiences gained in the field, at several customers, after collaborating with ServiceNow Support, investigating myself, etcetera.
If you have any thoughts yourself on this subject, don't hesitate to share!
Topics in this series of blogs (I will update this section when publishing new blogs):
- Maintain Audit Delete and Audit Relation
- Maintain Attachments and Attachment Documents
- Activating/adding Table cleaners
- Lower duration Table Rotation
- Drop syslog_trans_prejakarta* tables
- Cleanup Shadow tables
Maintain Attachments and Attachment Documents
The second blog in this series of blogs about keeping your instance database footprint tidy, one of the biggest contributors I've experienced so far at multiple customers: Attachment Documents [sys_attachment_doc]. Some parts of what I am going to share can already be found in some kind of shape on the ServiceNow Community, though some parts I haven't seen any publications on! The parts which you could have found already I do want to highlight, since at zero customers so far, I've seen this when I logged in to their instances for the first time.
When requesting a Database Footprint report from ServiceNow Support (view here how), for almost all customers the sys_attachment_doc table is listed in the top 10 of largest tables by size. It's not uncommon that this table is hundreds of gigabytes, or for one customer I've even seen the sys_attachment_doc as 1.1 TB!
Below are 7 reasons I've seen at several customers, why the sys_attachment_doc table is oversized. It's not an extensive list, you might have some reasons to add. If so, share your thoughts 🙂
- Orphaned Attachments
- ML Update Set
- Orphaned Attachment Documents
- Scheduled Email of Reports
- List export
- Export Sets
- Knowledge Management article versioning
Orphaned Attachments
(Almost) every ServiceNow instance out there will contain orphaned Attachment records [sys_attachment]. One of the causes will be when someone opens a new record or is raising a request on a portal and adds one or more attachments, these attachments are immediately stored in the Attachment table. When opening the new record or raising the request is abandoned though... the attachment is already stored in the Attachment table, and associated with a table_sys_id which is non-existing.
There can be more causes for orphaned Attachment records. For example when using Table Cleaners without cascade delete checked, or using scripted methods like the GlideMultipleDelete API. Have you encountered other examples? Do share!
There's no easy way to query orphaned Attachment records through a list. The table_sys_id is not a reference type field which makes dotwalking on a list not possible. Tracking down orphaned Attachment records needs to be done by scripted querying every single Attachment record, and from that querying the combination of table_name and table_sys_id.
While it's good to clean up and maintain orphaned Attachment records, this should not be a massive contributor. Should...
ML Update Set
... customers up to the Utah release with Predictive Intelligence solutions active might encounter that the number of orphaned Attachment records is actually a massive contributor. As it seems, there's an issue with the out-of-the-box Table Cleaner for ML Update Set [ml_update_set]. Basically, all ML Update Set records are maintained and cleaned up after 7 days, though the associated Attachment records are not! If you are on an older or larger ServiceNow instance, the number and size of these orphaned ML Update Set Attachment records can be tens or even hundreds of gigabytes.
About this finding, I published an article a while ago:
Any Predictive Intelligence solutions in use on your instance? Check your Attachments table ASAP!
Based on one of my Support Cases, ServiceNow Support raised problem PRB1666307 which has been confirmed to be fixed with the Vancouver release. Not yet going to the Vancouver release? Then consider a workaround, like a Table Cleaner or a Scheduled Job for example. Basically to delete Attachment records with "table_name=ml_update_set" which are older than 7 days.
Orphaned Attachment Docs
Good to know is that attachments are stored in the Attachment Docs table in chunks. The Attachment table is the parent of the Attachment Docs records and stores the file's meta information including, file name, size and type. Orphaned Attachment Docs records should be a rare situation, though can occur similar to what I mentioned for the orphaned Attachments: When using Table Cleaners without cascade delete, or using scripted methods like the GlideMultipleDelete API.
Orphaned Attachment Docs records can easily be queried on a list. Using as filter "sys_attachment.sys_idISEMPTY".
Scheduled Email of Reports
Scheduled reports are automatically run at a pre-defined frequency and emailed as files to a list of recipients. For these Scheduled Email of Reports, amongst others an Email record [sys_email] will be generated as well as the attachment representing the reports. Depending on the type of report and number of records these attachments might be rather small and innocent, though could also run into gigabytes.
For most companies, keeping these email records and their attachments indefinitely holds no value. So ask yourself, can these emails be maintained and cleaned up? Using a Table Cleaner or a Scheduled Job for example. Deleting these after for example 6 months. The condition to query the email table could be something like "target_table=sysauto_report^typeNOT INreceived,received-ignored^sys_created_onRELATIVELT@month@ago@6".
List export
ServiceNow offers users the possibility of exporting records on lists. When the number of records to export is above a certain threshold, the user is can wait for the export to be generated or can have the export emailed. Similar like Scheduled Email of Reports the Email records and attachments created by list exports that are emailed might be rather small and innocent, though could also run into gigabytes. When investigating such attachments, you'd be amazed at what people are exporting. For example the whole CMDB, or exports of hundreds of gigabytes!
For most companies, keeping these email records and their attachments indefinitely holds no value. So ask yourself, can these emails be maintained and cleaned up? Using a Table Cleaner or a Scheduled Job for example. Deleting these after for example 6 months. The condition to query the email table could be something like "subjectSTARTSWITHAttached is the^subjectENDSWITHyou requested^typeNOT INreceived,received-ignored^sys_created_onRELATIVELT@month@ago@6".
Export Sets
You can create an export set to export records from your instance to a file on a MID Server. By default these exports will be on an instance for 30 days, so these are already maintained. After 30 days these exports are deleted, which is a good thing since the attachments are already on the MID Server.
When you have a high number of export sets (perhaps even running daily) generating attachments of several gigabytes, you might consider lowering keeping these exports for 30 days. Could this be lowered to 7 days for example?
Knowledge Management article versioning
A lot of customers will have the Knowledge Management article versioning feature enabled. Basically the Knowledge Management article versioning feature is used to create and maintain multiple versions of a knowledge article automatically. While this is a neat feature, a downside is that all attachments are being copied to the new Knowledge article version. When using a good amount of attachments within your Knowledge articles, this can become a huge contributor to the growth of the Attachment Documents table.
Below a production example of a customer, where a specific Knowledge article has 25 versions, and the attachment (which by itself is already 0.4 GB) is now present on the instance 25 times... which is actually 10 GB of file size for one Knowledge article! Consider if keeping all attachments for older Knowledge article versions has value for your organization, especially when users are redirected to the newest version of Knowledge articles.
Optimize table
After having the Attachment table and the Attachment Docs table cleaned up, you do need to have at least the Attachment Docs table optimized. While you can do so 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.
Share
The Schedules described in this blog are also packaged as an Update Set that can be downloaded from Share:
- Maintain Attachments and Attachment Documents (Schedules)
Result
The actual result of maintaining the Attachment and Attachment Docs differs for every customer. Though just the example I've mentioned earlier of the customer with Attachment Doc of 1.1 TB... that was cleaned up to only 324 GB. Yes, you are reading that correctly: A reduction by more than 70% and it is possible to have it automatically maintained, so it's not just a one-off!
---
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
4x ServiceNow Developer MVP
4x ServiceNow Community MVP
---
19 Comments
