How do I change FILEGROUP of an existing Partition (SQL Server)

Question

I have a very large table which I partitioned by months. I have also created filegroups for each year, so each filegroup would hold at most 12 partitions for that year. Here is the partition function:

CREATE PARTITION FUNCTION [PF_MonthlyPartition](datetime) 
AS RANGE RIGHT FOR VALUES (N'2019-06-01T00:00:00.000', 
N'2019-07-01T00:00:00.000', N'2019-08-01T00:00:00.000', N'2019-09-01T00:00:00.000', 
N'2019-10-01T00:00:00.000', N'2019-11-01T00:00:00.000', N'2019-12-01T00:00:00.000', 
N'2020-01-01T00:00:00.000', N'2020-02-01T00:00:00.000', N'2020-03-01T00:00:00.000', 
N'2020-04-01T00:00:00.000', N'2020-05-01T00:00:00.000', N'2020-06-01T00:00:00.000', 
N'2020-07-01T00:00:00.000', N'2020-08-01T00:00:00.000', N'2020-09-01T00:00:00.000', 
N'2020-10-01T00:00:00.000', N'2020-11-01T00:00:00.000', N'2020-12-01T00:00:00.000', 
N'2021-01-01T00:00:00.000', N'2021-02-01T00:00:00.000')

The problem is, my last partition, instead of being in FG_2021 ended up in the PRIMARY filegroup.

Data Distribution right now

How do I change the file group of that partition to FG_2021?

My current partition sheme ended up looking like this:

CREATE PARTITION SCHEME [PS_MonthWise] AS PARTITION [PF_MonthlyPartition] 
TO ([FG_2019], [FG_2019], [FG_2019], 
[FG_2019], [FG_2019], [FG_2019], 
[FG_2019], [FG_2019], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [PRIMARY], [FG_2021])

Ben Thul · Accepted Answer · 2021-01-06T21:50:10.890

Do you have the ability to have the errant partition be unavailable/empty while the data is being moved to the correct FG? If so, switch out the partition to an empty table, issue the necessary create index... with (drop_existing = on...) on [FG_2021] commands to physically move the data, alter the partition function to merge out the "bad" partition, alter the partition function again to re-add the partition boundary (this time with the right FG), and then switch the partition (from step 1) back into the main table. The errant partition doesn't look that big, so this should be quick.

Update

Here's a quick repro in a dummy database:

/*setup*/
use master;
drop database if exists prtTest;
create database prtTest;
alter database prtTest add FILEGROUP [FG_2019];
alter database prtTest add file (
    name = 'FG_2019', 
    filename = 'c:\temp\FG_2019.ndf', 
    size = 10mb) 
to filegroup [FG_2019];

alter database prtTest add FILEGROUP [FG_2020];
alter database prtTest add file (
    name = 'FG_2020',
    filename = 'c:\temp\FG_2020.ndf',
    size = 10mb
) to filegroup [FG_2020];

alter database prtTest add FILEGROUP [FG_2021];
alter database prtTest add file (
    name = 'FG_2021',
    filename = 'c:\temp\FG_2021.ndf',
    size = 10mb
) to filegroup [FG_2021];

use prtTest;
go
CREATE PARTITION FUNCTION [PF_MonthlyPartition](datetime) 
AS RANGE RIGHT FOR VALUES (N'2019-06-01T00:00:00.000', 
    N'2019-07-01T00:00:00.000', N'2019-08-01T00:00:00.000', N'2019-09-01T00:00:00.000', 
    N'2019-10-01T00:00:00.000', N'2019-11-01T00:00:00.000', N'2019-12-01T00:00:00.000', 
    N'2020-01-01T00:00:00.000', N'2020-02-01T00:00:00.000', N'2020-03-01T00:00:00.000', 
    N'2020-04-01T00:00:00.000', N'2020-05-01T00:00:00.000', N'2020-06-01T00:00:00.000', 
    N'2020-07-01T00:00:00.000', N'2020-08-01T00:00:00.000', N'2020-09-01T00:00:00.000', 
    N'2020-10-01T00:00:00.000', N'2020-11-01T00:00:00.000', N'2020-12-01T00:00:00.000', 
    N'2021-01-01T00:00:00.000', N'2021-02-01T00:00:00.000');
CREATE PARTITION SCHEME [PS_MonthWise] AS PARTITION [PF_MonthlyPartition] 
TO ([FG_2019], [FG_2019], [FG_2019], 
    [FG_2019], [FG_2019], [FG_2019], 
    [FG_2019], [FG_2019], [FG_2020], 
    [FG_2020], [FG_2020], [FG_2020], 
    [FG_2020], [FG_2020], [FG_2020], 
    [FG_2020], [FG_2020], [FG_2020], 
    [FG_2020], [FG_2020], [PRIMARY], [FG_2021]
);

create table dbo.foo (dt datetime) on [PS_MonthWise](dt);
create clustered index [IX_foo] on dbo.foo(dt) on [PS_MonthWise](dt);

insert into dbo.foo (dt)
values 
    ('2021-01-01'), 
    ('2021-01-02'), 
    ('2021-02-01'), 
    ('2021-02-02');
select *, $partition.[PF_MonthlyPartition](dt)
from dbo.foo;

/*end setup*/

/*run this query after any partition function changes*/
select p.partition_number, prv.[value], fg.name, p.[rows]
from sys.partitions as p
left join sys.allocation_units as au
    on au.container_id = p.hobt_id
    and au.[type] in (1, 3)
left join sys.filegroups as fg
    on fg.data_space_id = au.data_space_id
left join sys.partition_range_values as prv
    on prv.boundary_id = p.partition_number
where object_id = object_id('dbo.foo')
order by partition_number;

create table dbo.temp_foo (dt datetime) on [PRIMARY];
create clustered index [IX_foo] on dbo.temp_foo(dt) on [PRIMARY];

alter table dbo.foo switch partition 21
    to dbo.temp_foo;

create clustered index [IX_foo] on dbo.temp_foo(dt) 
    with (drop_existing = on) on [PS_MonthWise](dt);

alter partition function [PF_MonthlyPartition]()
    merge range (N'2021-01-01T00:00:00.000');

alter partition scheme [PS_MonthWise]
    next used [FG_2021];

alter partition function [PF_MonthlyPartition]()
    split range (N'2021-01-01T00:00:00.000');

alter table dbo.temp_foo switch partition 21
    to dbo.foo partition 21;

Won't `CREATE INDEX...WITH (DROP_EXISTING=ON)` rebuild all the partitions? — Dan Guzman, Jan 04 '21 at 21:19
It sure will. But at that point in the procedure, the errant partition had been switched to a separate table. So "all of the partitions" equals "one" at that point. — Ben Thul, Jan 05 '21 at 02:36
Thank you Ben. That makes sense. I just tried it. I got an error telling me that even though my table is partitioned, my primary key isn't. I think I need to partition my primary key first. I wonder how long that will take lol — Allen B, Jan 05 '21 at 06:25
Ah yeah. I made an assumption that all of the indexes on the table were partitioned. That is indeed a prerequisite for this. — Ben Thul, Jan 05 '21 at 17:00
So this didn't exactly work. I thought it would, but it didn't. I rebuilt the primary key by partition function and was able to switch out the partition fine. Then I did a merge on the "bad" partition, and tried to re-add the partition boundary with the right FG. This is where it added back the partition on the PRIMARY filegroup again, even though I told it to use the FG_2021 with the ```ALTER Partition SCHEME PS_MonthWise NEXT USED [FG_2021]``` command. This is exactly what happened the first time I did it. I'm not really sure why it's happening — Allen B, Jan 06 '21 at 08:12
In theory, simply doing this should do the trick, but it doesn't : ```ALTER Partition Function PF_MonthlyPartition() MERGE Range ('2021-02-01 00:00:00.000') ALTER Partition SCHEME PS_MonthWise NEXT USED [FG_2021] ALTER Partition Function PF_MonthlyPartition() SPLIT Range ('2021-02-01 00:00:00.000')``` — Allen B, Jan 06 '21 at 09:38
I think you have to operate on the 2021-01-01 partition. I've added a repro in my answer. — Ben Thul, Jan 06 '21 at 21:50
Hi Ben. Thank you so much for taking the time to do this repro! You are awesome. Because this partition function is range right, the 2021-02-01 partition is partition 21. Basically it holds the data that is >= 2021-0101 and < 2021-02-01. But after going through your exercise, you are totally right. It worked! Interesting why, it id. I will now try it in production. Thanks a lot! — Allen B, Jan 07 '21 at 03:58

How do I change FILEGROUP of an existing Partition (SQL Server)

1 Answers1