0

I have a DB with 170GB+ data , 1 table contains 90% of db data. we are planning to do a sliding window partition on the ChangeLog table . What would be the best solution to manage the data , so that i can remove old data with least amount of down time , as I only have to keep 3 months data online.

Current file group MainData [2%] ChangeLog [90%] [1 table in this file group ,needs to purge data every 3 months] EarthData [8%]

drac13
  • 112
  • 9
  • 1
    The best solution is a sliding window but you seem to already know that. Is your question how to implement a sliding window? How are the other filegroups mentioned in your question relevant for the ChangeLog sliding window purge? Add DDL to your question for your partition function, scheme and ChangeLog table. – Dan Guzman Aug 05 '18 at 13:24
  • And in an edit to your question answer: How many rows does it have now? How many rows per month to you anticipate? Do you have a batch window to make the change? – David Browne - Microsoft Aug 05 '18 at 13:24
  • @DanGuzman I was asking whether there is any other solution to accomplish the same , as I am still confused about the indexing while table partition – drac13 Aug 05 '18 at 13:44
  • @DavidBrowne-Microsoft log table will have atleast 100 inserts every 15seconds – drac13 Aug 05 '18 at 13:45
  • @AdilAhamed please provide table DDL and missing information _in an edit_ to the question. How much downtime do you have daily, weekly, and monthly to perform this? You may be able to simply DELETE rows every night, week or month. – David Browne - Microsoft Aug 05 '18 at 13:53
  • 1
    Without table partitioning, you could simply delete data based on your retention needs. With a daily purge, you could do that in small batches to maximize concurrency and without downtime. Table partitioning could do that more efficiently and quickly but will require a brief (sub-second) schema modification lock during the `SWITCH`, `MERGE`, and `SPLIT` and more complexity. Regarding partitioned indexes, those must be aligned (all partitioned with the same underlying partitioned function). – Dan Guzman Aug 05 '18 at 13:58

0 Answers0