Over your storage limit? Table cleaner (and its limits)

[Janel]

This is a continuation of the mini-series "Over your storage limit? Reducing our storage footprint; our lessons learned."

https://www.servicenow.com/community/developer-forum/over-your-storage-limit-reducing-our-storage-fo...

This post is about how we leveraged table cleaner to help manage our records.  The goal of this post is to help give you ideas where you may be able to find some extra records to clean up.

I'm not going to go into table cleaner usage here, there are plenty of other articles out there already and here is one to get you started:

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0694151

Disclaimer:

This post is about the deletion of records to reduce the storage footprint size at our company.  This is what worked for us and it may not work for you.  Know your policies and retention periods and as always, review and make sure you test everything in a sub-prod instance first.

Table Cleaner (and its limits).

Table Cleaner is what SN primarily says to use to keep your growth under control.  They have several articles with misleading and incorrect information about it (which I have provided feedback on all of them), so don't hesitate to open a Support case and ask questions if something is confusing.

One downside of table cleaner is that you cannot use it against rotated tables (System Definition > Table Rotations), even though there are some out-of-box table cleaner records setup against rotated tables.  After talking to a capacity engineer with SN they believed those were there in error and may be deleted in newer platform versions.  So don't let those mislead you!

After deleting records on a rotated table, you will need to open a case with Support and request a table resizing (be sure to mention it is a rotated table and you cannot use table cleaner!).

More on rotated tables:

https://docs.servicenow.com/en-US/bundle/utah-platform-administration/page/administer/platform-perfo...

On the upside, table cleaner is very good and very fast at deleting records.  We've leveraged this to our advantage a few times to wipe out tables we no longer needed instead of using that slow UI action on the table.

Table cleaner does resize the table if there is enough free space to do it, making it your best friend when it can be used (for more information about table resizing, see the main post: https://www.servicenow.com/community/developer-forum/over-your-storage-limit-reducing-our-storage-fo...).

Tip: Indexing helps table cleaner go faster.

If you have a big query you're trying to execute and table cleaner is taking forever to run, make sure there is an index against the columns you're using.

Table Cleaner Run records

The sys_auto_flush_run table is a list of records of table cleaner runs.  You can check here if you want to make sure that a table cleaner record ran.  One thing we learned is that if there is a long running query Table cleaner just kind of gives up and doens't complete.  We have not gotten any confirmation if this means the table cleaner job quits or if it quits for that table.

Make sure you're checking in on sys_auto_flush_run to make sure that Last completed is populated.  If it isn't, open a case with Support to figure out why table cleaner isn't running for table table cleaner record.

Records we setup

Here are some big tables that we setup table cleaner rules for.  Hopefully these will give you something to start thinking about.  Remember, this is what we did for our company.  Make sure you check-in with whomever you need to for your time frames, content, and duration!

Unless otherwise noted, we have Cascade Delete checked for everything and the matchfield is always sys_created_on.  If we didn't use Age in seconds, we set that field to 1 and used the time frame in the condition builder.

• sys_archive_destroy_log
  
  • Created before 6 months ago.
• tracking_devices_by_disco_status
  • Created before 7 days ago.
    
    • This is for Discovery and will show up on the footprint "report" as s9g_devices_by_disco_status and t6g_devices_by_disco_status.
• sys_attachment
  • Table name is placeholder and created before 1 day ago.
    • https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0993541
  • Table name is ml_model_artifact and created before 2 years ago.
    • There is a known problem for this table leaving orphans: PRB1623070
  • Table name is ar_sys_email and created before 7 years ago (for HR reasons at our company).
  • Table name starts with ar_ and created before 10 years ago (as a catch all for our company).
• sys_tiny_url
  • Matchfield is last_accessed and Age in Seconds is 1 year ago (31,556,952).
• pa_scores
  • Indicator.name is empty (indicator.name=).
    • A pa_score without an indicator can't be used.  We found these showing up with a dead sys_id as the indicator, which is why we had to dot-walk to indicator.name.
    • There are properties and a job setup for PA tables, but we're not sure if they are working in our instance.
    • https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0721309
• sys_attachment_doc
  • Where sys_attachment.sys_id is empty.
    • There is some debate over this one.  We followed this article: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0954387 and later a case was automatically opened saying Table cleaner was causing a performance problem because of this due to running out of orphan records to delete (sounds like their problem, not ours).  They told us to create a weekly scheduled job instead that executes the table cleaner API once (instead of hourly).  They said manually using the API will still resize the table if needed/if possible.
    • Here is the poor documentation that they provided for that scheduled job, under the second "h)".  Hopefully it is better by the time you're reading it: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0785182
• sys_archive_destroy_log
  • Created over 6 months ago.
    • Archiving is very spammy and actually consumes more storage.  These are the logs that show a destroy job was run.
•  ml_model_artifact
  • Created before 2 years ago OR solution.active=false.
• sys_audit_delete
  • Created over 2 years ago.
    • Oh boy!  We never thought twice about this one!  We had been keeping all deleted records since we stood up ServiceNow over 10 years ago.  There aren't any properties or anything for this.  It was expected that you just knew to delete them.
• sys_audit_relation
  • audit_delete is empty AND audit is empty OR created over 2 years ago.
    • This is a relationship table that works between sys_audit_delete.  We flatly said if it is 2 years old or the related record is empty, it can go.
• Vulnerability tables (if you have them)
• sn_vul_detection and sn_vul_vulnerable_item
  
  • last_found is over 60 days ago.  No cascade delete.  No matchfield.
• sn_vul_vulnerability
  
  • closed_at is before 60 days ago.  No cascade delete.  No matchfield.
• cmn_notif_device
  • user.active is false AND user.deactivation date (custom field) is before 2 years ago.
    • Deactivation date is a this is a custom field for us that stores the date of when a user changed to inactive.  I'm sure there is an OOB field for this by now.
    • We had a lot of notification devices and notification messages tied to inactive users.  If they went inactive over 2 years ago, we delete them (this cleaned up about 344k devices and 928k notification messages).

Those were the heavy hitters that helped us a lot.

[Mark Roethof]

Hi there,

"After talking to a capacity engineer with SN they believed those were there in error and may be deleted in newer platform versions"

Interesting to read this. About a month ago for one customer we had a support case to have some Table Cleaners verified, since our custom Instance Scan check mentioned some active Table Cleaners for Table extension/rotation which will never work. 
The outcome of the Support Case: legacy, not going to do anything about it. We did push the case engineer to create a knowledge article on it: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB1437672.

Kind regards,
Mark