How do you manage your data retention Table cleanup routine



  • 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.



  • Using https://www.sqlshack.com/delete-cascade-and-update-cascade-in-sql-server-foreign-key/ 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 https://www.brentozar.com/archive/2018/11/adventures-in-foreign-keys-a-cascade-of-badness/ 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 https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15 to fix the logical race conditions that you mentioned you're currently running into.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2