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 Audit Delete and Audit Relation
Let's kick off this series of blogs about keeping your instance database footprint tidy with a bang, one of the biggest contributors I've experienced so far at multiple customers: Audit Delete [sys_audit] and its associated Audit Relations [sys_audit_relation]. Actually what I am going to share here is not new, if you do a proper search you can already find information on this subject on ServiceNow Support. I do want to highlight this one, 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_audit_delete and sys_audit_relation tables are listed in the top 10 of largest tables by size. It's not uncommon that these tables are hundreds of gigabytes, or for one customer I've even seen the sys_audit_delete as 2.1 TB and the sys_audit_relation as 1.7 TB!
Audit Delete capability
Good to know is some background on the Audit Delete table. The table has the word "audit" in it, which might give the wrong impression of its capability.
"The Audit Deleted Records [sys_audit_delete] and Audit Relationship Changes [sys_audit_relation] tables are used to store information about deleted records and their relationships to other records. These two tables facilitate an "Undelete" capability."
So in short, this concerns the un-delete capability. That is already way different than just "Audit delete". I would even argue if this has to do with Audit at all, though that's a different subject.
Recommendation ServiceNow Support
"Unchecked, these tables can grow very large and can cause wider performance issues within a ServiceNow instance. At some point, we might need to consider whether a record deleted many years ago will ever be restored, and is there any value in maintaining these audit records."
The above sentence says it all in my opinion. It's nice to have the un-delete functionality, though are you really going to un-delete artifacts from one year ago, three years ago, or ten years ago? That would be extremely unlikely 🙂
What the actual timeframe should be, is something to discuss within your company. As a ServiceNow customer, you are always the data owner and controller of the information you store in a ServiceNow instance. So you decide what information is to be stored, how it is to be used, and how long it is retained. I have seen customers agreeing on keeping the Audit Delete records for 6 months, and I have seen customers consider 2 years to be a safe prudent value.
Delete
Depending on the current size of the Audit Delete and Audit Relation tables on your instance, you should decide what approach to take regarding deleting records. If tables are not too big, you could do so yourself, perhaps writing a Fix Script, or a one-time Scheduled script. If this concerns hundreds of millions of records, consider having ServiceNow Support involved. ServiceNow Support has methods available to delete records way faster than you performing a GlideRecord with deleteMultiple().
When going for deleting the records yourself, here's a script to automatically clean up sys_audit_delete and sys_audit_relation records which ServiceNow Support made available.
Optimize
After having the Audit Delete and Audit Relation tables cleaned up, you do need to have these 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.
Maintain
The script mentioned in the previous section could also be used for a Scheduled Job. That way you can actually also maintain the Audit Delete and Audit Relation tables. You might also consider applying Table Cleaners instead. Table Cleaner is an out-of-the-box mechanism, and works well for the Audit Delete and Audit Relation tables. Here is an example of a Table Cleaner for Audit Delete:
For Audit Relation you would also need to set up a Table Cleaner. For example with the condition "Audit is empty .AND. Audit delete is not empty".
Result
The actual result of maintaining the Audit Delete and Audit Relation cleanup differs for every customer. Though just the example I've mentioned earlier of the customer with Audit Delete of 2.1 TB and Audit Relation of 1.7 TB... that's now down to only 40 GB and 34 GB. Yes, you are reading that correctly: A reduction from 3.8 TB to only 74 GB. And both tables are now also automatically being 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
---
15 Comments
