Clean up sys_audit_delete and sys_audit_relation records automatically - Support and Troubleshooting

Clean up sys_audit_delete and sys_audit_relation records automatically Issue .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } The Audit Deleted Records [sys_audit_delete] and Audit Relationship Changes [sys_audit_relation] tables store information about deleted records and their relationships. These tables enable the Undelete capability, which lets you restore deleted records by navigating to the Audit Deleted Records list, opening the form, and selecting Undelete Record.

Without maintenance, these tables can grow very large and cause performance issues. You can disable audit record generation by setting the table's dictionary attribute no_audit_delete . However, if you want to audit these records but only retain them for a specific period, you can use a scheduled script to purge records older than a set number of days.

Important: You are the data owner and controller of information stored in your ServiceNow instance. You decide what information is stored, how it is used, and how long it is retained. ServiceNow maintains 14 days of backup, so deletions take 14 days to cycle out of backups. Test any script in a non-production instance before using it in production.

Release .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } All supported releases

Resolution .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Before you begin Create an index to support this job. 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.

To create the scheduled job:

1. Go to System Definition > Scheduled Jobs .

2. Select New .

3. Select Automatically run a script of your choosing .

4. 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');

} }

5. Modify the Name and Repeat Interval values as needed You can also change the script variables vTableName and vDaysAgo .

6. Select Submit .

7. Repeat steps 1-6 for each table you want to clean up. Creating separate jobs per table lets you control the retention period for each.

To monitor the script logs, go to /syslog_list.do?sysparm_query=source=PurgeAuditDeletedRecords.

Related Links .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Altering tables and fields using dictionary attributes