August 30, 2012 at 12:33 am
Hey folks
I'm trying to use SPLIT on a partitioned table but the placement of the boundaries on filegroups end up as NOT desired.
Here's the situation simplified
- Partitioned table on column year_month
- LEFT Partition function to keep three months worth of data
201201 , 201204 , 201207,2012010
Scheme has five FGs
FG_Min ,FG_2012_Q1,FG_2012_Q2,FG_2012_Q3,FG_2012_Q4
- Wanted to split to create a new partition with boundary of 201301 , so followed these steps
## created new FG 2013_Q1
## Altered Partition scheme set used 2013_Q1
## Alter Partition function SPLIT RANGE (201301)
Everything went successful but when I tried to map the boundaries to file-groups , I found following
2013_Q1 is assigned values from 2012010 to 201301
2012_Q4 is assigned values > 201301
2012_Q1 has a lower partition than 2012_Q4 and the boundary of 2012_Q4 is now NULL
Any idea about what's the cause ? am I missing something ? I'm using SQL 2008 R2 SP1
THX a lot
August 30, 2012 at 1:53 am
Could you post your actual scheme and function alteration code please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2012 at 9:01 am
Voila
ALTER PARTITION SCHEME PS_BY_YEARMONTH_ Next Used 2013_JAN_MAR;
ALTER PARTITION FUNCTION PF_FTD_YEARMONTH() SPLIT RANGE (201301);
August 30, 2012 at 11:40 am
Looks OK. How did you determine that the wrong data is on the filegroup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2012 at 12:13 pm
Not really wrong , I just found that the partitions end up not as desired
Current order
parition_number ------- FG_Name -- Left_range -- right_range
6 ------ FG_JAN_Mar-- 201210 ----- 201201
7 ------ FG_OCT_Dec-- 201301 ----- NULL
I would like the opposite for less confusing distribution
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply