July 11, 2011 at 2:05 pm
We have tables that partitioned on a daily basis. However one day 2011-05-24, was skipped by some reason. It's records are saved in the next date's partition which is 2011-05-25. Now I need to split 2011-05-25 into two: 2011-05-24 and 2011-05-25.
Books-On-Line has only example how to split the very last partition:
ALTER PARTITION SCHEME MyRangePS1
NEXT USED test5fg;
Is there a way to split partition which is not the last one but in the middle ? Can somebody post an example ? I already created needed filegroup and file.
Thanks
July 12, 2011 at 2:02 am
This was removed by the editor as SPAM
July 12, 2011 at 6:14 am
stewartc-708166 (7/12/2011)
Regrettably one cannot do a split in the middle.You can only MERGE from the left and SPLIT on the right (i.e. latest).
mmmhh... are you totallly 100% sure about this?
I think there is no issue in splitting *any* partition in the table provided table is range partitioned - never had to do it in SQL Server but I've done this in other RDBMS.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 12, 2011 at 6:20 am
This was removed by the editor as SPAM
May 30, 2017 at 4:18 pm
I know this response is six years too late, but....
I cannot speak to SQL 2008 R2 or earlier, but SQL Server 2012 and newer can split a range in the middle. I just did this work for reasons similar to the OP. Here is a quick recap of the situation:
- Table partitioned by the month
- Supposed to keep 6 months of complete data, plus the current month
- It is proactive in nature, so the partition for the next month is always ready in advance
- Including a Tail that is always empty, this setup requires 9 file groups and at least 8 physical files (we don't provide a file for the Tail partition):
-- 1 file group and 0 physical files for the Tail
-- 6 file groups and 6 physical files for the 6 complete months
-- 1 file group and 1 physical file for the current month
-- 1 file group and 1 physical file for the coming month
The problem here was not enough file groups and physical files were provided. Instead of the required 9 and 8, respectively, only 7 and 6 were provided. Partition maintenance appeared work for the first five months, Nov - Mar; it did nothing as expected. When it rolled to the sixth month, Apr, and partition maintenance ran, there wasn't a file group old enough to move to the front so it did nothing. When May started up the May data was added to the Apr data since there was no file group for May. This behavior would have continued from that point forward; the most recent two month would always be combined together. Worse still, if no action was taken, then when June rolled around the partition maintenance would actual start working in a retroactive instead of proactive fashion. That is to say, instead of provisioning an empty file group to be available for the coming month, it would actually go to the previous file group and split it.
So:
- I added the necessary file groups and files including but not limited to the one missing for May.
- I altered the partition scheme for next used file group to point to the May filegroup
- I altered the partition function to split on the 05-01
- The May data that was in the April file group moved as expected to the May file group in a physical operation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply