Please help me in deleting millions of records from the asset table

[Aru123]

Hi team,

I made an error in my daily running scheduled script such that it ran continuously.

The scheduled script ran recursively and now we are left with 10,96,41,285 records in it. Deleting even ten thousand record is taking around 15 minutes. Can someone please help me, I don't want to lose my job, it's the only thing keeping me going right now

I am using the below script to delete in small bunches, but even deleting 50k takes around 4-5 hours:-

var gr = new GlideRecord("alm_consumable");
gr.addEncodedQuery("sys_created_onBETWEENjavascript:gs.dateGenerate('2023-05-06','00:00:00')@javascript:gs.dateGenerate('2023-05-19','23:59:59')");
gr.setLimit(50000);

gr.query();
while(gr.next())
{
gr.deleteMultiple();
}

[Joe S1]

Hello,

An Auto Flush (sys_auto_flush) which is triggered by the Table Cleaner scheduled job. Would be faster than running a background script. Honestly the fastest way though would be to create a HI ticket and have ServiceNow go in the back end and delete the records you don't need.

Thanks,

Joe S.

[Aru123]

Thank you Giga, I was not aware of the Table cleaner. I will check that out. Will also discuss with the team lead if they will let me raise a hi ticket for it.

[Mark Roethof · Accepted solution]

Hi there,

Reading the mistake you made and also the script you are sharing, tells a bit you are working on territory which you are not that experienced at. In that case, even be more cautious! 

While a Table Cleaner would be a better approach in my opinion, a small error made... and the consequences are huge. Scripted approach, same if using you really need to apply solid scripting. Also ask yourself if it's necessary that business rules/workflows/etc are triggered or not? Deleting these records, will this also generate audit, audit_delete, etc. and is this an issue or should this be prevented?

You might also consider using "Delete Jobs" functionaly.

Perhaps in your case consider reaching out to ServiceNow. So they can assist you and if something goes wrong while deleting they immediately can assist on that also.
Also note that ServiceNow has the availability to use a different delete method which runs way faster than scripted deleteMultiple.

Kind regards,
Mark

[Mark Roethof]

+ don't forget to optimize the table after deleting the records. Due to so many new records the performance on the table can have be impacted a lot.

Kind regards,
Mark