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

Aru123 · ‎05-23-2023

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&colon;gs.dateGenerate('2023-05-06','00:00:00')@javascript&colon;gs.dateGenerate('2023-05-19','23:59:59')");
gr.setLimit(50000);

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

Mark Roethof · ‎05-23-2023

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

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

10x ServiceNow MVP

---

~444 Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field

View solution in original post

Aru123 · ‎05-23-2023

Thank you very much Mark.

And yeah, the table was impacted so badly it won't even open until we try multiple times and wait for the page to not crash.

I will look into the Table Cleaner thing, I am not sure if the leads will let me create a hi ticket because they will have to justify why we have to raise a ticket of that type to the operations team, which might cause them issue, since I joined just 2 months ago so they may end up facing problem i think. But I will discuss it with them in the morning tomorrow. Thank you for replying.