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

  $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


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 ?


1 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;
  • Thanks - worked a treat. Note that I moved all the data out first , and then reload it after I had done the split
    – PeterL
    Commented Apr 4, 2023 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.