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.

Answer 1

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!

Answer 2

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