Partitioning : SPLIT seems to use the wrong filegroup ?

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Voila

    ALTER PARTITION SCHEME PS_BY_YEARMONTH_ Next Used 2013_JAN_MAR;

    ALTER PARTITION FUNCTION PF_FTD_YEARMONTH() SPLIT RANGE (201301);

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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