Background script/Scheduled job - setlimit not working with deleteMultiple

[SNAdmin47]

Hi,

I'm running some background scripts (on Dev/UAT) and looking to mass delete some sys_audit records in a careful manner. An API erroneously created a mass of audit records on an user for the avatar field and I'm looking to remove those audit entries as they're causing issues with the user form access. My initial background script returns the right number of records, and when I use the setLimit command to set the limit to 5 records this works fine and tells me via a getRowCount that I'm getting 5 records returned, i.e., the setLimit command works fine:

===========================

var gr = new GlideRecord('sys_audit');

gr.addEncodedQuery('documentkey=XXXXXXXXXXXX^fieldname=avatar');

gr.setLimit(5);

gr.query();

gs.print(gr.getRowCount());

===========================

However, when I try to use deleteMultiple command with the setLimit command the script deletes all records, i.e., if there's 18 records it deletes all 18 instead of the 5 defined by the setLimit command: 

===========================

var gr = new GlideRecord('sys_audit');

gr.addEncodedQuery('documentkey=XXXXXXXXXXXX^fieldname=avatar');

gr.setLimit(5);

gr.query();

while (gr.next()) {

  gr.deleteMultiple();

}

===========================

Please note: the XXXXX is the user sys_id... in the code I'm running I'm using the correct sys_id. This isn't the issue with the script. 

I've tried different layouts, e.g., not using 'while (gr.next())', but this keeps insisting on deleting all records that match the encoded query which I don't want to do. I want to be able to limit the volume of deletions in 'chunks' so that I can first be very careful by making sure I have the deletions taking place on the right user/table/field in very small volumes before I commit to using the same script (in an on demand scheduled job) to remove x volumes of records in larger chunks, hopefully without impacting system performance. 

Many thanks in advance to anybody who can confirm why it's doing this and what I should be doing to limit the amount of records being deleted. I'm scratching my head wondering what I'm doing wrong!

Gabe

[Anurag Tripathi · Accepted solution]

Hi,

If it is just 18 records, you don't really need SetLimit. 

When you use Delete Multiple it just take the whole bunch in the filter and delete all together, you don't need to write it in loop

EG

//Find all inactive incidents and delete them all at once
var gr = new GlideRecord('incident');
gr.addQuery('active', false);
gr.deleteMultiple(); //Deletes all records in the record set

However, if you want to delete in batches, I'd Suggest you use deleteRecord() instead of deleteMultiple and then your set Limit should work

var gr = new GlideRecord('sys_audit');
gr.addEncodedQuery('documentkey=XXXXXXXXXXXX^fieldname=avatar');
gr.setLimit(5);
gr.query();
while (gr.next()) {
  gr.deleteRecord();
}

[SNAdmin47]

Thanks @Anurag Tripathi , that helps a lot with my understanding on the usage of deleteMultiple vs deleteRecord. 🙂

FYI, there's 5 million records I need to work through, the 18 is just what I get on Dev/UAT when I generate test data to work with. I'm wary that deleteRecord takes longer than deleteMultiple... if I'm looking to delete a larger number, e.g., 100,000, would that still be viable to use deleteRecord in a scheduled job?  

[Anurag Tripathi]

Are you deleting all the data form a table?

If so use the Servicenow Way to Empty the table

PS, this will run in user session too and will time out of the table is huge, but you can just click it multiple times and let it delete. I would prefer to use this if I'm emptying a table.

[SNAdmin47]

As this is the sys_audit table we'll only be deleting selected records, i.e., the 5 million causing the issues with the user record, so clearing the table isn't something we'd be looking to do in this case. This is why we're having to use the script to identify the audit records associated to the user and the specific field. Having played about with the deleteRecord command this does work with the setLimit command so I can at least give it a go and see how it goes by performing some scheduled job executions and ramping up the volume in chunks and see how it goes! 

Thanks @Anurag Tripathi ,really appreciate the input. 🙂