Moving a Partition to a Different Filegroup
Published: Oct 01, 2017
This is a very common thing to need to do and will put together the pieces of my last two posts into one large post in order to achieve the desired outcome.

I’ve seen a good few questions about this on the internet as, although simple when you know how, there are a lot of people who struggle with the best way to achieve it.

For this demo we’re going to start with a table which is partitioned on just one filegroup… we’ll then add a new filegroup and move some of the data to it using MERGE and SPLIT.

Here’s the setup:

use AdventureWorks2012
go

if object_id('partitionSOHE') is not null
  
drop table partitionSOHE
go

if exists
(
  
select *
  
from sys.partition_schemes
  
where name = 'testPartitionScheme'
)
begin
   drop
partition scheme testPartitionScheme
end
go

if exists
(
  
select *
  
from sys.partition_functions
  
where name = 'testPartitionFunction'
)
begin
   drop
partition function testPartitionFunction
end
go

if exists
(
  
select *
  
from sys.filegroups
  
where name = 'Archive'
)
begin
   alter database
AdventureWorks2012
       remove
file AW2012Archive
  
alter database AdventureWorks2012
       remove filegroup Archive
end
go

alter database AdventureWorks2012
  
add filegroup Archive
go

alter database AdventureWorks2012
add file
(
  
name = 'AW2012Archive',
  
filename = 'D:\SQLData\AW2012Archive.ndf',
  
size = 250MB, maxsize = unlimited, filegrowth = 100MB
) to filegroup Archive
go

create partition function testPartitionFunction(datetime)
  
as range right
  
for values('2010-12-01', '2011-01-01', '2011-02-01', '2011-03-01', '2011-04-01', '2011-05-01')
go

create partition scheme testPartitionScheme
  
as partition testPartitionFunction
  
to ([Archive], [primary], [primary], [primary], [primary], [primary], [primary])
go

if object_id('partitionSOHE') is not null
  
drop table partitionSOHE
go

select *
into partitionSOHE
from sales.salesOrderHeaderEnlarged
go

create unique clustered index c_partitionSOHE
  
on dbo.partitionSOHE(salesOrderID, orderDate)
  
on testPartitionScheme(orderDate)
go

select p.partition_number, p.rows, f.name
from sys.partitions p
join sys.allocation_units a
on p.hobt_id = a.container_id
join sys.filegroups f
on f.data_space_id = a.data_space_id
where object_id = object_id('partitionSOHE')
go




Okay… now what we want to do is to move the December data into the Archive filegroup… this is a simple MERGE:

alter partition function testPartitionFunction()
  
merge range ('2010-12-01')
go




HOWEVER, let’s say we now want to create a NEW partition for the upcoming month… we MUST remember to ensure that the NEXT partition is created on the PRIMARY and then we can perform a split:

alter partition scheme testPartitionScheme
   next used [Primary]
go

alter partition function testPartitionFunction()
  
split range ('2011-06-01')
go

select p.partition_number, p.rows, f.name
from sys.partitions p
join sys.allocation_units a
on p.hobt_id = a.container_id
join sys.filegroups f
on f.data_space_id = a.data_space_id
where object_id = object_id('partitionSOHE')
go




Simples.

The key things to remember, which I’ve seen mentioned… merging and splitting a partition with data can be VERY expensive an operation, therefore please test first as these are NOT meta data changes, these are physical data moves and therefore can hit disks hard (as the filegroups could easily be on different physical disks).

Also, you cannot switch, merge, or split partitions between databases.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron