A script to automatically clean up sys_audit_delete and sys_audit_relation records - Support and Troubleshooting

A script to automatically clean up sys_audit_delete and sys_audit_relation records Issue 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. That is, if you delete a record, you can restore it by finding the records in the Audit Deleted Records [sys_audit_delete] list, opening the form, and clicking on the Undelete Record UI action. 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.

You can disable these records from being generated in the first place by setting the corresponding table's dictionary attribute no_audit_delete . However, oftentimes you may want to audit these records, but only retain them for a certain period of time. This article proposes a script that will purge records older than a set number of days for a specific table from the sys_audit_delete (and it's corresponding sys_audit_relation records).

Caveat : As a ServiceNow customer, you are always the data owner and controller of the information you store in a ServiceNow instance. You decide what information is to be stored, how it is to be used, and how long it is retained. We do not delete or modify your data and only process data in accordance with our contractual obligations and the configuration of your instance(s). We keep 28 days of backup. When you delete data from your ServiceNow instance, the deletion will take 28 days to be cycled out of a backup of that instance. Any script provided is for use at your own risk and should be fully tested in a sub production instance before being used in production.

Resolution The steps are straightforward:

Note: We recommend creating an index to support this job first (see the Additional Information section below). Navigate to the System Definition > Scheduled Jobs module. Click on the New button. Click on the Automatically run a script of your choosing link. Enter the following information: Name : Purge sys_audit_delete for sc_recurring_rollup after 7 days Run : Periodically Repeat Interval : 1 minute Run this script: var vTableName = 'sc_recurring_rollup'; var vDaysAgo = 7; PurgeAuditDeletedRecords(vTableName, vDaysAgo);

function PurgeAuditDeletedRecords(pTableName, pDaysAgo) { // Set total number of records to delete and maximum size of each batch (recommend leaving this as 100) var pRecordsToDelete = parseInt(2000); var pBatchSize = parseInt(100); // Check table name and delta are set, generate encoded query, set up variables if ((pTableName) && (pDaysAgo)) { var v_encoded_query = 'sys_created_on var pDeleteCount = parseInt(0); // Loop deleting records while (pDeleteCount

// Check if we need to reduce pBatchSize if ((pDeleteCount + pBatchSize) > pRecordsToDelete) { pBatchSize = pRecordsToDelete - pDeleteCount } // Find records in sys_audit_delete var gr = new GlideRecord('sys_audit_delete'); gr.addEncodedQuery(v_encoded_query); gr.setLimit(pBatchSize); gr.query(); // Exit if there are no records to delete if (gr.getRowCount() == 0) { break } // Increment delete counter pDeleteCount += parseInt(gr.getRowCount()); // Build array of sys_audit_delete sys_ids to be removed var pTargetSysids = []; while (gr.next()) { pTargetSysids.push(gr.sys_id.toString()) } // Delete referencing records from sys_audit_relation gr = new GlideRecord('sys_audit_relation'); gr.addQuery('audit_delete', 'IN', pTargetSysids); gr.query(); gr.deleteMultiple(); // Delete records from sys_audit_delete gr = new GlideRecord('sys_audit_delete'); gr.addQuery('sys_id', 'IN', pTargetSysids); gr.query(); gr.deleteMultiple(); } // Log work done gs.log('Deleted ' + pDeleteCount + ' records for ' + pTableName + ' from sys_audit_delete (and related sys_audit_relation records)', 'PurgeAuditDeletedRecords');

} }

You can modify the Name and Repeat Interval values as needed, as well as the variables in the script, vTableName and vDaysAgo. Click on the Submit button. Repeat steps 2-6 for all other tables for which you want to clean up from the sys_audit_delete/sys_audit_relation tables. Different jobs per table allow you to control the number of days you want to keep. You can monitor the logs to see the details from the script by navigating to the /syslog_list.do?sysparm_query=source=PurgeAuditDeletedRecords Related Links Adding an index on the Audit Deleted Records [ sys_audit_delete ] table on the ( tablename, sys_created_on ) fields will make the script more efficient.