If you get error message 7707 in SQL Server, it’s because you’re trying to create a partition scheme that doesn’t specify enough filegroups to match the partition function.

Fortunately, this is easy to fix.

Example of the Error

The error looks something like this:

Msg 7707, Level 16, State 1, Line 1
The associated partition function 'CatsPartitionFunction' generates more partitions than there are file groups mentioned in the scheme 'CatsPartitionScheme'.

Why Did this Happen?

In my case, I specified three filegroups when creating the partition scheme, but the partition function actually generated four.

Here’s the code I used to create the partition function and partition schemes.

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

CREATE PARTITION SCHEME CatsPartitionScheme  
    AS PARTITION CatsPartitionFunction  
    TO (CatsFg1, CatsFg2, CatsFg3);  
GO

My partition function has three boundary values, which results in four partitions. My partition scheme only specifies three filegroups (there should be four).

This is an easy mistake to make, because when you create the partition function, the number of boundary values you specify is actually one less than the number of resulting partitions. In other words, the number of partitions created will be equal to the number of boundary values + 1.

If you’re not completely on the ball, you might inadvertently match the number of filegroups with the boundary values, forgetting that you need to specify one more filegroup.

Fix the Error

So to fix this error, all I need to do is specify one more filegroup when creating the partition scheme.

My code should look more like this:

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

CREATE PARTITION SCHEME CatsPartitionScheme  
    AS PARTITION CatsPartitionFunction  
    TO (CatsFg1, CatsFg2, CatsFg3, CatsFg4);  
GO

In this case, I simply added CatsFg4 to the list of filegroups. This obviously assumes that the specified filegroup exists.

Also note that you don’t necessarily need to create a whole new filegroup. You can share filegroups (i.e. have multiple partitions mapped to a single filegroup).

Therefore, I could have done this:

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

CREATE PARTITION SCHEME CatsPartitionScheme  
    AS PARTITION CatsPartitionFunction  
    TO (CatsFg1, CatsFg2, CatsFg3, CatsFg3);  
GO

Notice that the last two filegroups are the same (CatsFg3).

But you don’t need to stop there. Another approach is to have all partitions mapped to the filegroup.

In this case we could use the ALL argument, and specify just one filegroup:

CREATE PARTITION FUNCTION CatsPartitionFunction (int)  
    AS RANGE LEFT FOR VALUES (1, 100, 10000);
GO  
 
CREATE PARTITION SCHEME CatsPartitionScheme  
    AS PARTITION CatsPartitionFunction  
    ALL TO (CatsFg1);  
GO