Transact SQL – Error – “The associated partition function [pfn] generates more partitions than there are file groups mentioned in the scheme [scheme]”

Background

Trying to automate scripts for auto-creation Database File Partition Objects ( FileGroups \ Files \ Functions \ Schemes).

Error

While testing out the script for generating the Partition Scheme, arrived at the error pasted below:

The associated partition function ‘pfnYear‘ generates more partitions than there are file groups mentioned in the scheme ‘pschemeYear‘.

 

Code

Original Code

Partition Scheme

Explanation

  1. Partition Schema
    • Name :- pschemeYear
  2. Partition Function
    • Name :- pfnYear
  3. Filegroup
    • List of Database File groups

SQL


CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
TO
(
       fg_Partition_Year_2010, fg_Partition_Year_2011
     , fg_Partition_Year_2012, fg_Partition_Year_2013
     , fg_Partition_Year_2014, fg_Partition_Year_2015
     , fg_Partition_Year_2016, fg_Partition_Year_2017
     , fg_Partition_Year_2018, fg_Partition_Year_2019
     , fg_Partition_Year_2020
)

Partition Function

Explanation

  1. Partition Function
    • Name :- pfnYear
  2. Values
    • List of dates

SQL


CREATE PARTITION FUNCTION [pfnYear](datetime)
AS RANGE RIGHT FOR VALUES
(
     N'2010-01-01T00:00:00.000', N'2011-01-01T00:00:00.000'
   , N'2012-01-01T00:00:00.000', N'2013-01-01T00:00:00.000'
   , N'2014-01-01T00:00:00.000', N'2015-01-01T00:00:00.000'
   , N'2016-01-01T00:00:00.000', N'2017-01-01T00:00:00.000'
   , N'2018-01-01T00:00:00.000', N'2019-01-01T00:00:00.000'
   , N'2020-01-01T00:00:00.000'
)
GO

 

Revised Code

Outline

There are a few workarounds:

  • Create an extra file group for outliers
  • Assign outliers to one of the existing file groups
  • Assign all partition schemes to single filegroup

Sample

Use Default Primary group

Explanation

We can use the default primary group.

SQL

CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
TO
(
       PRIMARY -- Use default file group ( PRIMARY )
     , fg_Partition_Year_2010, fg_Partition_Year_2011
     , fg_Partition_Year_2012, fg_Partition_Year_2013
     , fg_Partition_Year_2014, fg_Partition_Year_2015
     , fg_Partition_Year_2016, fg_Partition_Year_2017
     , fg_Partition_Year_2018, fg_Partition_Year_2019
     , fg_Partition_Year_2020
)

Create extra File Group

Explanation

We can use the default primary group.

SQL
SQL – Create Filegroup

USE [WideWorldImportersDW]
go
if not exists
(
	select *
	from   sys.filegroups tblSFG
	where  tblSFG.name = 'fg_Partition_Year_Base'
)
begin

      ALTER DATABASE [WideWorldImportersDW]
	     ADD FILEGROUP  [fg_Partition_Year_Base]   

end 

SQL – Add File to Created Filegroup

ALTER DATABASE [WideWorldImportersDW]
ADD FILE
(
	  NAME = [fg_Partition_Year_Base__01]
	, FILENAME = 'C:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_Base_file_001.ndf'
	, MAXSIZE = UNLIMITED
	, FILEGROWTH = 100MB
)
TO FILEGROUP [fg_Partition_Year_Base]
SQL – Create Partition Scheme against Partition Function across fie groups

CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
TO
(
      [fg_Partition_Year_2010], [fg_Partition_Year_2011]
    , [fg_Partition_Year_2012], [fg_Partition_Year_2013]
    , [fg_Partition_Year_2014], [fg_Partition_Year_2015]
    , [fg_Partition_Year_2016], [fg_Partition_Year_2017]
    , [fg_Partition_Year_2018], [fg_Partition_Year_2019]
    , [fg_Partition_Year_2020]
    , [fg_Partition_Year_Base]  -- extra file group

) 

Create all partitions on same File Group

Explanation

We can simply create all partitions on same file group

SQL
SQL – Create all Partitions on same filegroup

CREATE PARTITION SCHEME [pschemeYear]
AS PARTITION [pfnYear]
ALL TO
   (
     [fg_Partition_Year_Base]
   )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s