Partition Function has got out of Synch

Question

we have a large database that has a number of partitioned tables The tables are partitioned by an EventKey column that is int datatype, with each unique value being allocate it's own partition. For each partition we create a unique FileGroup/FileName combination. We have a weekly job , that creates extra Partion Files/Groups in advance, and runs the ALTER PARTITION SCHEME [PartitionSchema] NEXT USED to keep everything in sync

unfortunately , we had a schema deadlock and thinks got out of order, and we now need to fix it. We now have 2 events mapped to the same partition, all subsequent events are mapped to a unique partition, but it is out by 1

the sql

SELECT 
  $PARTITION.pfPublishes(2420) as pf_2420,

  $PARTITION.pfPublishes(2421) as pf_2421,

  $PARTITION.pfPublishes(2422) as pf_2422,

  $PARTITION.pfPublishes(2423) as pf_2423,

  $PARTITION.pfPublishes(2424) as pf_2424,

  $PARTITION.pfPublishes(2425) as pf_2425

returns

pf_2420	pf_2421	pf_2422	pf_2423	pf_2424	pf_2425
2420	2421	2422	2422	2423	2424

IE both events 2422 and 2423 are mapped to partition 2422. All subsequent events are mapped to their own partition, but it is out by a value of 1.

What is the best way of resetting the pfPublishes partition function to correctly map the event ?

Answer 1

You just need to SPLIT on that value. You should first set the next filegroup to be used

ALTER PARTITION SCHEME partition_scheme_name NEXT USED NewFileGroup;

ALTER PARTITION FUNCTION pfPublishes() SPLIT RANGE (2423);