Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioning : SPLIT seems to use the wrong filegroup ? Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 12:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:13 AM
Points: 3, Visits: 1,265
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
Posted Thursday, August 30, 2012 1:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 40,176, Visits: 36,575
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
Posted Thursday, August 30, 2012 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:13 AM
Points: 3, Visits: 1,265
Voila

ALTER PARTITION SCHEME PS_BY_YEARMONTH_ Next Used 2013_JAN_MAR;


ALTER PARTITION FUNCTION PF_FTD_YEARMONTH() SPLIT RANGE (201301);
Post #1352297
Posted Thursday, August 30, 2012 11:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 40,176, Visits: 36,575
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
Posted Thursday, August 30, 2012 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:13 AM
Points: 3, Visits: 1,265
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 »

Add to briefcase

Permissions Expand / Collapse