Adding a New Filegroup to Existing Partition?

Posted on 2010-04-02
Medium Priority
Last Modified: 2012-05-09
Hi, I recently asked a question about how to create a table partition for a complex data situation that we have.  I think I have figured out the best way to implement this however, I have a question about how to handle new data.

IE:  We have a table partitioned into years... 1995, 1996, 1997, etc... up to current year 2010.

So, I create a bunch of filegroups and create my partition scheme based on the years that currently exist... 1995-2010.

2010 rolls along and I just keep inserting new records into the partitioned table for 2010...

Well, what happens when 2011 comes?  I looked at alter partition function but it only allowed for splitting and merging exisiting partitions into new ones...not just creating a new one entirely.  

So, how does one handle adding a "new" set of ranges to an existing partition... do you have to create a new one entirely and then move the data over?
Question by:Roxanne25
  • 2
LVL 15

Expert Comment

ID: 29484557
Adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the partition scheme.

Author Comment

ID: 29491382
Yes I looked at alter partition schema... it only lets you add a partition for an existing file group and as I mentioned the alter partition statement only lets you merge or split filegroups....

Accepted Solution

colinspurs earned 2000 total points
ID: 29687652
This should do what you need.  It is taken from here:


Split partition

Execute the following T-SQL statement to split last partition into two partitions.

Alter partition function [Data Partition Range]()
split range(300)
When executed you will get the following error.

Msg 7707, Level 16, State 1, Line 1
The associated partition function 'Data Partition Range' generates
more partitions than there are file groups mentioned in the scheme
'Data Partition Scheme'.
In order to avoid this, we have to add another file group to the database.

Let us execute the below T-SQl statement, which will add a new file group to the database.

Alter database [Data Partition DB5] add FILEGROUP [Data Partition DB5 FG5]
alter database [Data Partition DB5]
       (NAME = 'Data Partition DB5 FG5',
        FILENAME =
         'C:\Data2\FG4\Data Partition DB5 FG5.ndf',
        SIZE = 5MB,
        FILEGROWTH=1 )
TO FILEGROUP [Data Partition DB5 FG5]
Now let us try again to split the partition range by executing the following T-SQL statement.

Use [Data Partition DB5]
ALTER PARTITION SCHEME [Data Partition Scheme]
NEXT USED [Data Partition DB5 FG5]
Alter partition function [Data Partition Range]()
split range(500)
Note: We should make SQL Server aware that it should use the next partition when it is splitting. The clause NEXT USED does that.

Author Comment

ID: 29688759
Ah awsome!  That is what I needed...then I can split the ranges around to make sure the right years are in the right places... coolies.  Ty. :)

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question