question

TatyanaY avatar image
TatyanaY asked

Purging tables referenced with foreign keys

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!
foreign-keyarchivingpurge
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.