Hi, we need to archive/purge a very transaction-active database that contains a big number of tables densely referenced with foreign keys. Some tables are referencing more than one table. I understand that the first to be purged would be the referencing tables, and then the referenced ones. Which means, I believe, that we'd need to establish a strict order in which purging takes place. Which, in its turn, means that I could not have several dynamic SQL but would rather have to have an individual delete statement for each table, following one another in a hand-made order... am I right? :) I wonder if there are any best practices on how to approach this situation. Please share your experience, or advice. Thank you!
Yes. Your best bet is to break it down so that the delete statements for each table are in the correct order. Depending on how much data you're deleting you may need to batch the deletes into smaller sets in order to keep the transaction log small.