May 2, 2023 at 11:58 am
I've joined a new company and have noticed that a scheduled partition creation job hasnt been running for the past few months.
We now have 6 months' worth of data(it should be monthly) in the last partition. My question is to avoid having to move data can we just create a new partition from the start of next month and move on? They currently don't switch out the partitions for archiving or anything they just live in the DB on a table partitioned.
I would like to start archiving on a monthly basis retaining maybe 2 years in the active DB. When I get to the point of hitting this large partition I'll delay any switch until we no longer need that data in the table and move out the 6 months in one hit.
Is this in any way a sensible approach or should I just bite the bullet and start trying to retrospectively fix the issue to bring it all to a good monthly partitioned state before doing the archiving process?
Any thoughts?
May 2, 2023 at 12:44 pm
Eeasiest would be to start adding a partition for the new month and go from there on.
You'll need to handle the 6-month partition adequately in your cleanup process. ( Leave the rows until actually all rows of this 6-month partition can be switched out, to avoid log-overhead [for row deletes] )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 2, 2023 at 1:10 pm
Thanks, Johan,
That was my preference. We need to implement processes but I don't think the effort is going to be worth splitting this one and we'll address the large partition as you've said
Appreciate your response
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply