How to delete more than 13 thousand records from a table at once?

Ashirav · ‎01-14-2019

I tried that "System Nav-->Tables & Columns-->Delete all records--> Type Delete" thing, but that didn't work.

When I select the records to delete, they take forever to show the popup that asks Whether or not to delete:-

Surely there must be another method, something that will not require me to stay awake the whole night to delete these records manually?

The table is the "core_company" table.

[Edit after 2 replies: ] The conditions are "Name starts with xyz" and "Created by abc". Please help me in scripting also if some script is required.

Thanks!

balu3 · ‎01-14-2019

Hi Ashi,

Just like what Alberto said. You can do that.

Else just go to the list and do the following as in the image.

1) Enter the query you want in the filter condition.

2) Run the query

3) Right click on the query string and click copy query.

4) Put that in gr.addEncodedQuery('<paste_here>');

5) Run the script

Some points to be noted when deleting more than 1000 records

1) It might take like more than an hour or 2 in normal cases.

2) It might take more than 10 hours in special cases. Special cases means that there are many operations done along with this. Like say running Business Rules.

a) It can occur because of multiple operations done as part of Business Rules

b) Because of cascaded deletes

c) Because of poorly structured Business Rules and logic.

d) Because of multiple indexes regeneration simultaneously (although I'm not sure of this, I just heard this one).

You can use gr.setWorkflow(false); before the delete statement to avoid some of them. Although this should be used if and only if you do not want other condition to run as part of your delete statement.

I'd recommend you to split the delete operation into batches of 10 or so.

Like,

var gr = new GlideRecord('core_company');

gr.addEncodedQuery('nameSTARTSWITHxyz^sys_created_bySTARTSWITHabc');

gr.setLimit(1000);

gr.deleteMultiple();

Run this code multiple times until there are no records left.

Also, please keep in mind that running a scheduled script or background script in a production instance requires your client's approval (as per most of the client's policies I'm aware of). Please check that once as well.

Thanks

Balu

View solution in original post

Alberto Consonn · ‎01-14-2019

Hi,

best way is to create a new Scheduled Job on Demand, put the following script and execute it:

var gr = new GlideRecord("<table name>");   
gr.setWorkflow(false);
gr.deleteMultiple();

If I have answered your question, please mark my response as correct so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.

Thank you

Cheers
Alberto

balu3 · ‎01-14-2019

Hi Ashi,

The UI method may probably not work in this case.

Reason: UI method considers the ACL's to check if you have the access to delete the records.

Solution: Skip the ACL. Write a script and run in background scripts section.

Risks: Well, this is highly not recommended as following this procedure has a high chance of getting unexpected files to be removed.

Minimizing risks: Well, create a query in the list view and select the query and add it in gr.addEncodedQuery(query);

Example, to delete all incidents that have category network.

var gr = new GlideRecord('incident');

gr.addEncodedQuery('category=network');

gr.deleteMultiple();

Thanks

Balu

Ashirav · ‎01-14-2019

Hi Balu25int,

I have slightly updated this question as to the complete requirement. Your answer like it can be of really a lot of help, can you please once check the question again and help me form the script?

Thanks!

Alberto Consonn · ‎01-14-2019

Add the following two lines in my script and run it:

gr.addQuery('name', 'STARTSWITH','xyz');
gr.addQuery('created_by','abc');