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

- Maintain Emails

- Reclaiming Table Free Space

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