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
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
| 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 ?
