Geek asks and answers

Partition table error with more partitions than filegroups

I'm trying to split the database table, I created the filegroups correctly (I think), I had to add a couple of additional filegroups along the way, because I had an error with the number of partitions compared to the filegroups (I have problems with this), I created the partition function is no problem, but when I try to create a partition scheme, I get the following error:

Msg 7707, Level 16, State 1, Line 2 The associated partition sum 'PARTFN_INV_LINE_FACT' generates more than the group of files mentioned in the scheme 'PARTSCH_INV_LINE_FACT'.

Did I miss a step?

I am new and doing this to find out about a future task, so please excuse me if I have not provided enough information. I have included everything that I have done below.

All filegroups must be explicitly entered into the schema.

+3
source share
4 answers

All filegroups must be explicitly entered into the schema.

+1
source

Without seeing the code, I can’t be 100% sure, but I suspect that you were faced with a specific problem that I made when I tried to reuse the current partition function with the new partition scheme. My partition function defined 16 range values, however my partition scheme only defined 8 partitions, which led to the same error you indicated.

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

CREATE PARTITION FUNCTION partitionFunctionName (datetime) (                      '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

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

, , , , - !

+6

You may have forgotten to include the [PRIMARY] filegroup in your partition scheme.

+5
source

The criteria should indicate the number of filegroups, partition functions.

+1
source

More articles:


All Articles