Partitioning : SPLIT seems to use the wrong filegroup ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Partitioning : SPLIT seems to use the wrong...

Partitioning : SPLIT seems to use the wrong filegroup ?

Rate Topic

Display Mode

Topic Options

Author

Message

mmsad ss

Posted Thursday, August 30, 2012 12:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 3, Visits: 1,099

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

Post #1352025

GilaMonster

Posted Thursday, August 30, 2012 1:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

Could you post your actual scheme and function alteration code please?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1352045

mmsad ss

Posted Thursday, August 30, 2012 9:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 3, Visits: 1,099

Voila

ALTER PARTITION SCHEME PS_BY_YEARMONTH_ Next Used 2013_JAN_MAR;

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

Post #1352297

GilaMonster

Posted Thursday, August 30, 2012 11:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

Looks OK. How did you determine that the wrong data is on the filegroup?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1352399

mmsad ss

Posted Thursday, August 30, 2012 12:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 3, Visits: 1,099

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

Post #1352409

« Prev Topic | Next Topic »

Permissions