Deleting change log entries

TESDeveloper · 2019-01-22

We need to delete 50,000,000 change log entries via sql. How long would you expect this to take and is there anything i should consider before doing this?

The entries were created in error and aren't needed.

Thanks

Juhl · 2019-01-22

Impossible to say. Depending on disc speed and server resources.
So pick... 10 min, 20 min, 30 min....

Test it on a copy of the db

Slawek_Guzek · 2019-01-22

I would rather pick hours than minutes, imho.

If you are about to clear all the table entirely use TRUNCATE, not DELETE. If you want to leave some of the records then do not delete remaining 50mil all in one go, all in one transaction, as this will likely lead to transaction log expansion, which is very costly and time-consuming operation. Delete them in batches 10k-100k records at a time instead, using

DELETE TOP (nnnnnn) FROM...; GO nnnn

(GO nnnn is the SSMS directive/command hence works from SSMS only)

Alternatively, select rows which are supposed to remain into a temp table, then truncate the change log entry table, and then copy back required data.

Developer101 · 2019-01-23

I have some experience in this delete large number of entries. Used Truncate so it was not that bad.

kriki · 2019-01-24

Truncate would be the best. If you can't use it, best first disable secondary keys and then delete the records (How:see other posts) and then re-enable the secondary keys again.

RockWithNAV · 2019-01-25

I also did Truncate long back but it took way more time I remember as there were around 5 crore data. Try truncating post business hours and leave then!!

Howdy, Stranger!

Quick Links

Categories

Deleting change log entries

Answers