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 (or time to place backups back in case of emergencies!), 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
- Reduce (over)auditing
- Lower duration Table Rotation
- Drop syslog_trans_prejakarta* tables
- Cleanup Shadow tables
Emails
Every notification email that a ServiceNow instance creates or receives is recorded in the "Emails" table [sys_email]. For larger ServiceNow instances, it's perfectly normal that the Emails table contains tens or hundreds of millions of records. Gigabyte size wise a daily growth of a few gigabytes on the table size is for larger ServiceNow instances also not uncommon. Before cleanup, for one of my larger customers the Emails tables* was good for 567 gigabytes on the Database Footprint!
* For older ServiceNow instances, the Emails table is most likely on Table extension. This was the out-of-the-box behavior until this got changed a few releases ago.
Junk
So why is the Emails table such a big contributor to the Database Footprint of ServiceNow instances? Numerous reasons and it might differ per customer of course. Though the biggest reason: Junk! Analyzing the junk (written out below), for this customer this concerned more than one-third of all email records.
What is junk?
- Email contains no recipient. Emails skipped and send-ignored, and having an error string "Email contains no recipients".
- Unprocessed received email (no table/record). Received-ignored emails, where the target_table is absent.
- Received-ignored email send to ourselves.
- Abandoned emails by the email client, "User did not press the Send button in Email Client".
- Undeliverable/returned received mail (bouncebacks).
What could be considered junk (with a timestamp of more than 1 month ago, or more than 3 months ago)?
- Orphaned email records.
- List exports. When a user performs a list exports and emails the list to themselves, an Email record is created and kept indefinitely (including the attachment record!).
- Scheduled Email of Reports. It is possible to generate and distribute scheduled reports via email. These email records are kept indefinitely (including the attachment record(s)!).
You might come up with more that could be considered junk, though this is the majority. The retention period for such junk emails might be company-specific. For the first group, you might want to keep this for 1 or 2 months at least so it can be analyzed.
For several customers, we are maintaining the Emails table and the junk above with a daily scheduled cleanup (Scheduled Job).
ServiceNow Support
While analyzing the huge size of the Emails table, I often run into performance issues. Manually opening the table it's being populated very slowly or not at all, background script execution taking ages, etcetera. This does make the analysis part touch. Luckily, ServiceNow might give you some unexpected extra insights.
Some subjects ServiceNow Support can provide numbers on, which might give you insight into where further to investigate:
- Size per shard
- Rotations/extension
- Count of NULL Instance records which has a scope for Maximum deletion
- NULL Records with a GROUP BY on Subject
- Subjects with Undeliverable have a scope for deletion
- Top contributors of Email with a Group BY on subject
- Top contributors of Email in sys_attachment
- Top contributors from sys_email shards
- Top contributors from sys_email shards by target_table
Further investigation
After performing scheduled cleanups and looking into the suggestions from ServiceNow Support, there is still a whole world to gain for Emails. A few subjects I came across that hopefully can help others too:
- Reduce duplicate emails send.
- Specific emails addressed to thousands of users. Might this be a design flaw and should a distribution list be used instead? (also impacts performance)
- Sudden increase in skipped emails when comparing email per week/month. What were the changes performed on your instance during that time period? Or perhaps a release/patch/hot fix?
- What are the data retention rules in your company for emails? Can certain emails be cleaned up, for example for high-occurring emails like "Incident has been assigned to you", "Approval has been requested".
Sub-production
When you are after reducing the Database Footprint (for example due to overlicensing), if you are not doing this already for other reasons, for sub-production instances you could consider excluding the Emails table from System Clones.
Optimize table
After having the Emails table cleaned up, you do need to have the 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.
---
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
---
2 Comments
