2

As the title suggests, I need to figure out how I can remove ~1 billion rows of a Send Log data extension. I can't even change the data retention policy until the count is < 100 million. I thought I could query a smaller subset, write them to a temp DE, then query that and overwrite the send log, then I could change the retention settings, but even when limiting the query to the most critical attributes and after I deleted all unnecessary attributes from the Send Log (going from 120 to 13, 6 of which are part of the Send Log template and uneditable), the query still times out.

Seems like there might be a SSJS solution, but that's outside my wheelhouse so I don't know if any of the uses cases that utilized a SSJS solution are even applicable.

At this point it seems like clearing the data and starting fresh might be the only option.

4
  • Please update your question to include the deletion criteria and the fields you have in your send log. Oct 30, 2023 at 20:44
  • How important is the send log data? Oct 30, 2023 at 21:13
  • Is there any chance it could be done via Data Loader? In addition to the gui interface, Data Loader can be called from the command line, which allows it to be scripted. Maybe set up a scheduled job to delete 5 million rows every so many hours?
    – barbecue
    Oct 31, 2023 at 3:22
  • SendLog data is pretty important, but only realistically the previous day. I have an extract that runs daily that pulls the VAWP url for import into our CS platform so agents can see member comms. At this point I'm about 2 weeks behind so ideally I'd be able to catch up, but certainly everything prior than that could go.
    – PForsyth
    Oct 31, 2023 at 13:53

1 Answer 1

2

I've done something like this using SSJS. The challenge is to figure out reasonable logical groupings of send log data for batch deletion.

You might follow this approach:

  1. Retrieve all of the aggregated send data for the entire account history and approximate the number of sends per job id.
  2. Populate a data extension with job id, send date and approximate volume and a Boolean processed flag, defaulted to false.
  3. Write a Script Activity that loops through the job ids from step #2 and deletes send log rows by job id and then updates the processed flag to true.
  4. Run this over and over, forever.

There could be some solutions that use the hidden _customObjectKey identity column in the send log, but those require something smaller than a billion records.

3
  • Could I use the CreateDate attribute and just remove anyone with a Create Date >= whatever date?
    – PForsyth
    Oct 31, 2023 at 13:44
  • You could, but you don't want to have to lookup with a range. Using something exact like a jobid would be more performant. Dates are datetime datatype in the database. JobID would generally be aligned with a single date -- exception being triggered sends. Oct 31, 2023 at 14:21
  • How many billions of records are there? Also the retention seems not to work when set after the intial de creation, eventhough it is written in the documentation or at least it was not working for me that time :) Would not be easier to create new send log de with proper retention policy set from begining and then move records that are need from the old send log? Nov 5, 2023 at 20:07

You must log in to answer this question.

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