What is the fastest way to delete 1000000 records in cmdb (incl cascade deletes) ?

[tommyjensen]

However some records should NOT be deleted?

Using a background script and deleting with deleteMultiple() runs 20 minutes to delete 100 records! It must be possible to do that faster even with cascade deletes.

[Deepak Ingale1]

GlideRecord Glimmerings: Windows...

GlideRecord queries in Chunk, may be that will help you to delete data in chunk.

[binodsinghkhura]

By using background script

[bernyalvarado]

krbond, binodsinghkhurana@gmail.com...

Hi guys, I'll strongly suggest you consider doing something like this through a on demand scheduled job or through a fixed script. Using a background script for very large/long transactions such deleting 1 million CMDB CI records will freeze your session and could also result on a halted/truncated transaction.

Between a scheduled job and fixed script I will always go with the scheduled job since on the past I also had fixed scripts which execution was halted.

I hope this helps

Thanks,
Berny

[oliverschmitt]

Folks,

to be crystal clear: If you use gs.sql and delete or truncate it is not supported and you can gonna mess up the instance! If you delete cmdb_ci_server with SQL delete or truncate it will leave orphan records in the above table structure like cmdb_ci which can't be delete and will mess up you data structure. The GlideRecord wrapper for SQL does a bit more than just querying the database. There is a good reason why we designed no direct access to the database!

There are a few things to make the deleteMultiple method faster, because by default it will still delete record by record which is slow indeed:

1. Set attribute "no_audit_delete" to "true" and " iterativeDelete=false":

http://wiki.servicenow.com/index.php?title=Dictionary_Attributes

By default it's false (= not present). This means for each delete from a table a record will be inserted into sys_audit_delete for history purposes. This will slow down the delete process.

2. Set the workflow to false as well. By default even when using deleteMultiple method, each record deleted will trigger the workflow behind (if the case). That will also slow down the delete process. Setting workflow to false will trigger the deleteMultiple method to use batch mode (500, not 1000 as we said before).

I have done the following test on my own instance and deleting 50K records took about 3 min 30s. This is the script I use (on a different table):
----------------------
var gr = new GlideRecord('cmdb_ci');
gr.addEncodedQuery('sys_updated_by=oliver.schmitt^asset_tagSTARTSWITHAsset:');
gr.setWorkflow(false);
gr.deleteMultiple();

Try to use FIX scripts or scheduled scripts over background scripts. Fix scripts will run in a worker thread with basically no limit for long running transactions. Background scripts could be terminated due to quota rules since it runs as a simple user transaction and the code will not be saved.

[Deepak Ingale1]

Thanks Oliver,

Do we require to still modify dictionary attribute even if we are using setWorkflow(false) to avoid deleted audit logs generated?