0

How do you manage table clean up when you have multiple tables that have different retention policies and FK Parent-Child relationships? Originally I had a table that had different delete statements (Delete from xxx where createddate < Getdate()-30) in the most simple example. Then I would have a nightly process just run through each statement and run them - this does not seem to be a really efficient way - I am starting to run into issues when one delete depends on another but for whatever reason, one does not finish fast enough and then I cant clean up the table because of child relationships. Also would just like to get better ideas on a more robust system to do this with, and have not been having good luck figuring out what to google to get examples.

2
  • Are you using cascading deletes for your foreign key relationships?
    – J.D.
    Mar 27, 2022 at 0:31
  • No. There are no cascading deletes on the database i'm afraid. Mar 27, 2022 at 2:41

1 Answer 1

2

Using cascading deletes on your foreign keys might be most efficient from a code standpoint since it's essentially minimal code to create, and would eliminate a lot of additional DELETE statements you're currently manually managing.

It should also be transactionally consistent, which would solve the problem you mentioned: "starting to run into issues when one delete depends on another but for whatever reason, one does not finish fast enough".

But they may not be the most efficient from a performance standpoint so test carefully before releasing to production.

If you do continue to manually manage your own DELETEs then you should wrap each parent and its children tables in the same explicit transaction to fix the logical race conditions that you mentioned you're currently running into.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.