Why has the file group for the partition grown in size after the alter?

Question

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?

Answer 1

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

Answer 2

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:

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.)

After:

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.