Using Background Scripts to Delete Massive Amounts of Records

[jmiskey]

Someone was testing a Scheduled Job script in our DEV environment, and did not realize that it was caught in a endless loop, and it added the same record to our sys_user_grmember table over 28 MILLION times over a period of 4 days!  We were able to kill the job, but now we would like to clean-up our sys_user_grmember table (it now takes multiple minutes just to open it).

Be able to write a script to identify which records to delete should not be a problem, since we only need to delete records for a certain User name value.  We already use another background script to delete hundreds of records at a time that works well.  If I adopt the same methodology, the code to delete multiple records from the sys_user_grmember table would look something like this:

var gr = new GlideRecord('sys_user_grmember');  
gr.addQuery('user','xxxxxxxxxxxxxxxxxxx'); //enter sys_id of user here 
gr.query();    
gr.deleteMultiple();

My question is, is there any concern running this script to delete millions of records at once?  Will it freeze up resources, or crash?  Or is there a better way of handling this?

Thanks

[Harshal Gawali]

Hi,

Yes you can use GlideRecord's deleteMultiple() method to delete multiple records. Also go through below link. you will get an idea about what things need to keep in mind before deleting multiple records.

Mass-Deletion and Excess Data Management Recommendations

Regards,

Harshal.

[jmiskey]

Thank you.

That article is very helpful.  I will present it to my boss and see how he wants to proceed.

[jmiskey · Accepted solution]

Well, we attempted the "DeleteMultiple" solution, and while it was working, it was not very efficient.  We ran it for a few days, and it was running so slow, that it was only deleting about 2.4 million records a day.  So at the pace it was going, it would have taken over 10 days to complete!

So we opened a HI ticket, and discovered a much more efficient way of doing this.  We identified and exported all the records we wanted to keep (about 13,000) to 2 XML files (as with our system settings, we can only export 10,000 at a time).  Then we use a TRUNCATE command to delete ALL the records in the Table.  That was very quick (it was done in under a minute or two).  Then we re-imported the good records that we had exported, and we were back to the place that we needed to be.

This method worked very well for us, but obviously you need to be VERY careful when using this method, as you are wiping the table clean.  We exported the good records we wanted to keep to XML files to ensure that none of the sys_ids on these records we change, and we would not end up with any orphan records with other tables.