8

I have done my due diligence on this and simply cannot find an answer that does not involve Apex, which is what I'm trying to avoid for various reasons.

Does anyone know how to adequately without a ton of labor delete tens of millions of records in a (managed, custom) object in Salesforce? (specifically, it's an object from Exact Target / Marketing Cloud)

The closest I can think of based on my investigations are:

  1. SQLForce - seems highly dangerous (though perhaps no more so than any other solution)

  2. Excel Connector (I use it religiously) - I could write VBA to pull a group, delete a group, etc, but given that the table has over 100 million rows already, querying it is a problem (maybe SystemModStamp)

I haven't found anything adequate on AppExchange or via Google. I'm betting Apex is the only answer beyond SQLForce or an insanely time consuming routine like Excel Connector or Data Loading a list of the IDs and upload deletions that way. Am I missing something obvious??

Thanks.

UPDATE 2015-05-22 5pm EST - For the sake of continued due diligence, I've started a Data Loader deletion (Bulk API) of 1 million records to see the result. Fingers crossed! I will update again after the US Holiday.

UPDATE 2015-05-22 530pm - I am rather pleasantly surprised that it WORKED. Now for a real test (next week) of perhaps 10 million, and if that works, may as well go for the 150 million I need to expunge.

We have a specific Trigger that is overbearing, involving our Accounts and Opportunities, and such Bulk actions would be a joke to even consider usually, but either (a) since this is not directly related, or (b) perhaps due to the latest round of developers we've had who have touched up code here and there, maybe we're beyond that.

I will update again after the next test.

6
  • 2
    If you are trying to clear all records from a custom object, there is the truncate option in the UI. (help.salesforce.com/…) Probably not what you are looking for, but always useful. May 22, 2015 at 21:22
  • (1) not all, and (2) not available for this (probably due to being managed?) :-/ But thanks.
    – AMM
    May 22, 2015 at 21:35
  • 1
    You have to enable custom object truncation in Customize>User Interface. It works for managed package objects in my org. But, it is the nuclear option! May 22, 2015 at 21:38
  • 1
    Nuclear, hah, yeah. We want to keep "30 days" of data on this object ongoing (now) as we mirror it (without deletion) to Postgres using CopyStorm (an AWESOME utility). SF will have the past month, PSQL will have all time. MicroStrategy will allow reporting on it (and everything else). Boom. ;-) We're only at 720% storage currently (cough).
    – AMM
    May 22, 2015 at 21:39
  • 1
    Something to keep in mind... when you do a delete, all that is really happening is that the 'IsDeleted' flag is set on the record (which is why you can recover records from the recycle bin). The record still exists for up to 30 days. While your storage usage will drop, the records are still there. I mention this because I ran into an issue where I had 14M records in the recycle bin and my queries were still slow (because they were being checked). If you really want to get rid of the records, you need to do a 'Hard Delete'.
    – MarcDBehr
    May 23, 2015 at 10:52

3 Answers 3

3

A previous organisation I worked had had a couple of child objects with over 250 million rows of data in.

When the time came to delete some of it we populated a custom indexed field on the relevent parent object records (eg all with a created date in 2013) with a sequential range of values, and then used Informatica to run a query to extract record ids from the child object for a range of values in the parent object custom field that equated to 100,000 child records and then hard delete them.

We then had a batch script to increase the range of values for the parent object and reran the extract/delete process. Informatica allowed us to schedule the job to run every 10 minutes, using this method we were able to delete 70 million records over a weekend.

0
0

Write a batch which will delete 10000 records per execution and logic should count the total no. of records , divide by 10000 and execute the batch that no. of times.

0

Deleting that much of data from Salesforce is no joke and require a great deal of planning. Writing batch delete has its own demerits and it can easily surpass the 24 hr rolling limit of your Org and moreover it will stall the batches that are required to complete business processes and use case. So In my experience, deleting 250m+ records needs to be well thought through. Following are key principles to consider

  1. Always keep a tab on number of batches that are being executed for deletion
  2. Consider running delete operations from reliable computer and having good internet connection (Data loader hangs multiple times even when all things are okay)
  3. Consider non batch approach also (Multi threaded, Multi instance programs each thread having 199 records deletion at one go) (I wrote a JSforce/nodejs based utility that spawn 10 loops and 10 such instances were launched)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .