0

A table is partitioned by month/year. They would like to add 1/1/2024 but what the scheme is the correct sequence until the end where it ends like:

[FG_11_2023], [FG_12_2023], {PRIMARY], [FG_01_2024]) 

The partition function ended with:

 '2023-12-01T00:00:00:000' 

The new file group for 1/24 was not getting any data from the file size and after reviewing the scheme, I assumed the data was going into PRIMARY.

I attempted to fix this by altering the scheme with:

ALTER PARTITION SCHEME [my scheme name]
   NEXT USED [FG_01_2024];

Then I altered the function with split.

ALTER PARTITION FUNCTION [name of my partition]() 
   SPLIT RANGE ('2024-01-01T00:00:00:000');

Why has the file group for 1/24 grown in size after the alter?

enter image description here

2

2 Answers 2

0

Seems the problem is that the FH_12_2023 actually contains November data, meaning the December data of 2023 doesn't have a FG_13_2023 to go to so it went to PRIMARY. Now that you defined January 2024 to FG_01_2024 it is going where you expect...

0

If you would like to make it simple meta data fix, you could try parition switching to fix this.

  1. First, you need to create a new table using a partition boundary of 2023-12-01 (FG-01-2024), for which a new partition function and scheme must be created.

  2. Next, switch partition number 37 to the new table. This is purely a metadata operation and does not involve any actual data movement.

  3. Afterwards, merge partitions number 37 and 38 on the existing table. This operation combines the two partitions into a single one. Since you have switched out 37, this merge also doesn't involve data movement.

  4. Then, split the merged partition using the boundary of 2023-12-01 and assign it to a new filegroup FG-01-2024, instead of using the PRIMARY filegroup. Since the merged parition doesn't have any data in that boundary you will have no/little data movement here. And, you should have an empty parition created with FG-01-2024.

  5. Finally, switch the partition that was originally switched out in step 1 and switch it back into the new partition (empty) that you created in step 4.

Also, be sure to note that the steps from 2 to 5 need to be run using serializable isolation to block active changes that would otherwise block above process.

Same needs to be repeated to fix boundary 2024-01-01, with FG-02-2024. I would suggest to run this on test environment before prod.

Ref: More about partition switching

With above mentioned approach on a staged table,

Before: enter image description here

When partition is switched out to staging table for the fix: (An index rebuild is needed here to migrate the data from PRIMARY FG to required FG FG_01_2024. But that would be only on that parition/chunk of data.) enter image description here

After: enter image description here

How? https://gist.github.com/dvsrk/8b6177bea096550d3b609cadea0309c5

Note: There are many things I have assumed with respect to table definition, no PKs, no FKs, no NCIs etc. Depending on your current table's definition the execution steps would change.

2
  • Thank you for the instructions, it is very much appreciated.
    – Marcel
    Jan 30 at 19:27
  • @Marcel added more details to my post, included script I have used to re-produce.
    – S.D.
    Jan 30 at 20:04

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.