Partitioning Table Error with more partitions than Filegroups

Question

I am trying to partition a DB table, I have created the filegroups correctly (I think), I had to add a couple of extra filegroups along the way as I had an error with the amount of partitions compared to filegroups (I have trouble getting my head round this), I have created a partition function without problem, but when I try to create the partition scheme I get the following error:

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

Have I missed a step?

I am new and doing this to learn for a future task, so please excuse me if I haven’t given enough information. I have included everything I have done below.

All filegroups have to be explicitly entered in the scheme.

Papillary

Answer 1

All filegroups have to be explicitly entered in the scheme.

Papillary

Answer 2

Without seeing the code, I can't be 100% sure, but I suspect you've run into the exact problem I did when I tried to reuse a current partition function with a new partition scheme. My partition function defined 16 range values, however my partition scheme only defined 8 partitions, resulting in the same error you've cited.

In my case, the solution was to simply not try to reuse the existing partition function, and instead create a new partition function and partition scheme, with an equal number of range values and partitions like this:

CREATE PARTITION FUNCTION partitionFunctionName(datetime) AS RANGE LEFT FOR VALUES ( '20130228 23:59:59.997',
'20130331 23:59:59.997',
'20130430 23:59:59.997',
'20130531 23:59:59.997',
'20130630 23:59:59.997',
'20130731 23:59:59.997',
'20130831 23:59:59.997',
'20130930 23:59:59.997'
) GO

CREATE PARTITION SCHEME [partitionSchemeName] AS PARTITION partitionFunctionName TO ( [PartitioningFileGroupName1] ,[PartitioningFileGroupName2] ,[PartitioningFileGroupName3] ,[PartitioningFileGroupName4] ,[PartitioningFileGroupName5] ,[PartitioningFileGroupName6] ,[PartitioningFileGroupName7] ,[PartitioningFileGroupName8] ,[PRIMARY] )

GO

I know this is an old question, but maybe this will help someone avoid the same issue!

Oliverolivera

Answer 3

Probably you forgot to include the [PRIMARY] filegroup in your partition scheme.

Boulder

Answer 4

The number of criteria must be missing a number of filegroups, partition functions.

Spellbinder

Answer 5

you have to match the filegroups.

SELECT name AS AvailableFilegroups FROM sys.filegroups WHERE type = 'FG'

The result of the query should be equal to the scheme.

Spindrift

Answer 6

Have you mentioned the filegroups which are available in your system?

You may get filegroup list from the query:

SELECT name AS AvailableFilegroups
  FROM sys.filegroups
  WHERE type = 'FG' 

Spindrift

Answer 7

Also I faced the same error: The number of filegroups or range values is not valid. Enter an extra filegroup in addition to the number of boundary values. I just added a filegroup for the last empty row.

Synder