The question:
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.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0