Data Archiving, and the sys_audit table

[Jamsta1912]

Hi all,

We're in the procees of setting up some archiving rules, mostly for older task data. We have nearly 150 million entries on our sys_audit table, and I thought it might be a good idea to also archive those entires that are associated with now archived task records. Is that the right approach? Similar question for the sys_journal_field table (though that has far few records).

Thank you

[Mark Roethof]

Hi there,

Archiving could be an option. Though first ask yourself: why? What is your reason for considering archiving? Would cleaning up unnecessary audit first be an idea? Would table extension be an idea? Again, whats the thought of why going for archiving?

150 million isn't shocking btw 🙂

[Jamsta1912]

Hi Mark.

Thank you for your reply. Yes, I guess 150 million is small compared to many instances!

I had read that extension is not recommended for the sys_audit table, at least in general, here: Demystifying table rotation, extension, and table cleaner.

Clean-up might suit us, but I think there will be an appetite to retain the sys_audit data for archived incidents etc, at least until the archived incidents are themselves deleted.

[Mark Roethof · Accepted solution]

For task related data I can understand that you want to keep the sys_audit... or kinda. There are several fields on task, incident, etc. which don't hold any value at all to audit. Though what I see out there, most customers just have audit on a table on or off, and hardly added on specific fields no_audit=true. This way also useless fields are audited. (one of my upcoming blogs is on overauditing 😁).

My suggestion:

- Look into tables or fields which are audited, though hold no value and can audit can be turned off (this can also be on field level!).

- Look into specific fields which are updated very often, for example if sys_user is audited (out-of-the-box is not, though a lot customers do enable this), the last_login and especially last_long_time. Or there might be fields like last_seen_in_ad or similar. Same for cmdb, fields like last_discovered.

- Bring audited tables up for discussion. For example at a current customer, Task SLA audit was on (out-of-the-box is not) and was generating 5 million+ sys_audit records monthly. I brought this up for discussion, and the answer... we have no clue why this is audited or why we need it. It was simply turned on by the implementation partner way back.

- Cleanup the sys_audit, remove data for the tables / fields which you turned off.

- Look into audited data, for example not task specific, which can be deleted after for example 2 years.

- Optimize/rebuild the audit table, technically you can do so yourself, though involve ServiceNow Support on this

- If still wanted, think of archiving or extension.

Since you do mention sys_audit, don't forget sys_audit_delete and sys_audit_relation! See:

- 2023-06-13 - Blog - Keeping your instance database footprint tidy (01): Maintain Audit Delete and Audit Relation

[Jamsta1912]

Hi Mark,

Thank you. I really appreciate this detailed response. It's very helpful 🙂