I have a transaction database that we recently went live with. The number of rows in the tables are starting to climb into the millions. So I am looking at how to purge the older data.

I have written purge processes before by just deleting the data I don't need anymore. But when you need to delete several million rows it can take a while and negatively impact performance and/or cause locks.

I have looked into table partitioning. The idea of partitioning the data and then just dropping a partition seems perfect.

However, from what I read, this cannot be done if the tables have foreign keys (my tables are mostly a chain of Master-Detail tables)

Is there a way to use partitioning with foreign keys, to delete lots of data?

Or some other cool way to delete large chunks of data without performance hits or locks?

  • 1
    If you need to delete massive amounts of data, then you always delete in batches to not overload/lock tables, etc. We have several processes that delete millions of rows a night in 10k-100k row batches. Reference 1, reference 2, reference 3 – Taryn Mar 19 '15 at 20:36
  • Would dropping the constraint for now work or is that too risky in the environment? Also, AFAIK you can partition with FKs as long as several requirements are met. tinyurl.com/n6yj38q – Ali Razeghi Mar 19 '15 at 21:04
  • You need to know your schema well enough to know the order of operations required to delete, in sequence, in order to not break FK constraints. Once then, you can understand the order of dropping the partitions. I agree with mister bluefeet above, yes, definately delete in batches. – beeks Mar 20 '15 at 3:15
  • You can also use Cascading deletes to help you along, but yes delete in batches. Additionally, you may need to look at your indexing prior to deleting. I you have both clustered and non-clustered indexes on those tables, you could be looking at massive run times due to all the reorg required to maintain the indexes. – Steve Mangiameli Mar 20 '15 at 17:18
  • @beeks - I thought that you could not drop the partition even if you had cleaned up the dependencies in the data correctly. Is there a way to do it via partition dropping if you know the order of operations? – Vaccano Mar 20 '15 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.