How to Fix “The partition scheme ‘…’ does not have any next used filegroup” in SQL Server

You’ll get error 7710 if you try to split a partition in SQL Server, but you haven’t specified a “next used” filegroup.

The whole error looks something like this:

Msg 7710, Level 16, State 1, Line 1
Warning: The partition scheme 'MyPartitionScheme' does not have any next used filegroup. Partition scheme has not been changed.

Where MyPartitionScheme is the name of the partition scheme in question.

If you get this error, you’ll need to add a “next used” filegroup using the ALTER PARTITION SCHEME statement.

The Problem

Here’s a quick rundown on the problem.

When I try to split a partition:

ALTER PARTITION FUNCTION MoviesPartitionFunction()
SPLIT RANGE (500);

I get the following error:

Msg 7710, Level 16, State 1, Line 1
Warning: The partition scheme 'MoviesPartitionScheme' does not have any next used filegroup. Partition scheme has not been changed.

This is because I haven’t specified a “next used” filegroup for MoviesPartitionScheme, which in my case, is the partition scheme that I used to apply the MoviesPartitionFunction to the filegroups to be used by the partitions.

Here’s how I created my original partition function and partition scheme:

CREATE PARTITION FUNCTION MoviesPartitionFunction (int)  
    AS RANGE LEFT FOR VALUES (-1, 100, 10000);

CREATE PARTITION SCHEME MoviesPartitionScheme  
    AS PARTITION MoviesPartitionFunction  
    TO (MoviesFg1, MoviesFg2, MoviesFg3, MoviesFg4);

So it currently has four partitions and I’m trying to add a fifth.

The Solution

We can resolve the above problem by adding a “next used” filegroup for the partition scheme.

We can use an existing filegroup or create a new one.

Let’s create a new one, and try to split the partition again:

ALTER DATABASE Test ADD FILEGROUP MoviesFg5;

ALTER DATABASE Test   
ADD FILE   
(  
    NAME = MoviesFg5dat,  
    FILENAME = '/var/opt/mssql/data/MoviesFg5dat.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP MoviesFg5;

ALTER PARTITION SCHEME MoviesPartitionScheme  
NEXT USED MoviesFg5;

ALTER PARTITION FUNCTION MoviesPartitionFunction()
SPLIT RANGE (500);

Result:

Commands completed successfully.

Excellent, so it worked, and we no longer get the error.