All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂