Creating a data partitioned table, error on creating parttion function

  • quinn.jay

    SSCertifiable

    Points: 5505

    Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

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

    Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

    Below is the code flow I'm executing

    Thanks


    USE ABHS_BI;
    GO

    -- Adds six new filegroups to the ABHS_BI database
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2015fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2016fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2017fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2018fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2019fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2020fg;

    -- Adds one file for each filegroup. for 6 filegroups
    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2015dat1,
      FILENAME = 'G:\\sw2015dat1.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB
    )
    TO FILEGROUP sw2015fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2016dat2,
      FILENAME = 'G:\\sw2016dat2.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2016fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2017dat3,
      FILENAME = 'G:\\sw2017dat3.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2017fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2018dat4,
      FILENAME = 'G:\\sw2018dat4.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2018fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2019dat5,
      FILENAME = 'G:\\sw2019dat5.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2019fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2020dat6,
      FILENAME = 'G:\\sw2020dat6.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2020fg;
    GO

    -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

    --DROP PARTITION FUNCTION myDateRangePF1 ;

    CREATE PARTITION FUNCTION myDateRangePF1 (int)
      AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
    GO

    -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
    CREATE PARTITION SCHEME myDateRangePS1 --S
      AS PARTITION myDateRangePF1 --F
      TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
    GO

    -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
    CREATE TABLE [dbo].[BI_Actvt](
    [actvt_ym] [int] null,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Rev](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Usg](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

  • Sue_H

    SSC Guru

    Points: 89891

    quinn.jay - Tuesday, July 24, 2018 2:58 PM

    Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

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

    Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

    Below is the code flow I'm executing

    Thanks


    USE ABHS_BI;
    GO

    -- Adds six new filegroups to the ABHS_BI database
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2015fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2016fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2017fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2018fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2019fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2020fg;

    -- Adds one file for each filegroup. for 6 filegroups
    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2015dat1,
      FILENAME = 'G:\\sw2015dat1.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB
    )
    TO FILEGROUP sw2015fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2016dat2,
      FILENAME = 'G:\\sw2016dat2.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2016fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2017dat3,
      FILENAME = 'G:\\sw2017dat3.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2017fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2018dat4,
      FILENAME = 'G:\\sw2018dat4.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2018fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2019dat5,
      FILENAME = 'G:\\sw2019dat5.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2019fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2020dat6,
      FILENAME = 'G:\\sw2020dat6.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2020fg;
    GO

    -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

    --DROP PARTITION FUNCTION myDateRangePF1 ;

    CREATE PARTITION FUNCTION myDateRangePF1 (int)
      AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
    GO

    -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
    CREATE PARTITION SCHEME myDateRangePS1 --S
      AS PARTITION myDateRangePF1 --F
      TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
    GO

    -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
    CREATE TABLE [dbo].[BI_Actvt](
    [actvt_ym] [int] null,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Rev](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Usg](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    Your partition function has 6 so you have 7 partitions. It's based on covering everything less than and greater than what you specify in the function. Some things you read might say something like "create an extra partition". But it's really about covering everything and understanding whether to designate right or left range rather than just slapping another partition in there. This is an old post but still good - it's pretty easy to see with the partitions in their example and it explains the range right/left and how that fits in:
    Table Partitioning Basics

    Sue

  • quinn.jay

    SSCertifiable

    Points: 5505

    Sue_H - Tuesday, July 24, 2018 4:41 PM

    quinn.jay - Tuesday, July 24, 2018 2:58 PM

    Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

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

    Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

    Below is the code flow I'm executing

    Thanks


    USE ABHS_BI;
    GO

    -- Adds six new filegroups to the ABHS_BI database
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2015fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2016fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2017fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2018fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2019fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2020fg;

    -- Adds one file for each filegroup. for 6 filegroups
    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2015dat1,
      FILENAME = 'G:\\sw2015dat1.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB
    )
    TO FILEGROUP sw2015fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2016dat2,
      FILENAME = 'G:\\sw2016dat2.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2016fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2017dat3,
      FILENAME = 'G:\\sw2017dat3.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2017fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2018dat4,
      FILENAME = 'G:\\sw2018dat4.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2018fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2019dat5,
      FILENAME = 'G:\\sw2019dat5.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2019fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2020dat6,
      FILENAME = 'G:\\sw2020dat6.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2020fg;
    GO

    -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

    --DROP PARTITION FUNCTION myDateRangePF1 ;

    CREATE PARTITION FUNCTION myDateRangePF1 (int)
      AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
    GO

    -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
    CREATE PARTITION SCHEME myDateRangePS1 --S
      AS PARTITION myDateRangePF1 --F
      TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
    GO

    -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
    CREATE TABLE [dbo].[BI_Actvt](
    [actvt_ym] [int] null,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Rev](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Usg](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    Your partition function has 6 so you have 7 partitions. It's based on covering everything less than and greater than what you specify in the function. Some things you read might say something like "create an extra partition". But it's really about covering everything and understanding whether to designate right or left range rather than just slapping another partition in there. This is an old post but still good - it's pretty easy to see with the partitions in their example and it explains the range right/left and how that fits in:
    Table Partitioning Basics

    Sue

    Thanks, this helped a lot. I now have other issues, but its separate from this so will make a new post.

  • Sue_H

    SSC Guru

    Points: 89891

    quinn.jay - Wednesday, July 25, 2018 3:29 PM

    Sue_H - Tuesday, July 24, 2018 4:41 PM

    quinn.jay - Tuesday, July 24, 2018 2:58 PM

    Hello, I've never created a partitioned table on SQL Server before, I'm on SQL Server 2016, and trying to do it with T-SQL. As I get towards the end, right before I create partitioned tables, and I get an error on the CREATE PARTITION SCHEMA and I get error:

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

    Can someone please help me in detecting what I need to alter/change in the code, what I'm doing wrong or need to rework.

    Below is the code flow I'm executing

    Thanks


    USE ABHS_BI;
    GO

    -- Adds six new filegroups to the ABHS_BI database
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2015fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2016fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2017fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2018fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2019fg;
    GO
    ALTER DATABASE ABHS_BI
    ADD FILEGROUP sw2020fg;

    -- Adds one file for each filegroup. for 6 filegroups
    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2015dat1,
      FILENAME = 'G:\\sw2015dat1.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB
    )
    TO FILEGROUP sw2015fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2016dat2,
      FILENAME = 'G:\\sw2016dat2.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2016fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2017dat3,
      FILENAME = 'G:\\sw2017dat3.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2017fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2018dat4,
      FILENAME = 'G:\\sw2018dat4.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2018fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2019dat5,
      FILENAME = 'G:\\sw2019dat5.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2019fg;
    GO

    ALTER DATABASE ABHS_BI
    ADD FILE
    (
      NAME = sw2020dat6,
      FILENAME = 'G:\\sw2020dat6.ndf',
      SIZE = 10MB,
      MAXSIZE = 250MB,
      FILEGROWTH = 10MB
    )
    TO FILEGROUP sw2020fg;
    GO

    -- F Creates a partition function called myDateRangePF1 that will partition a table into six partitions

    --DROP PARTITION FUNCTION myDateRangePF1 ;

    CREATE PARTITION FUNCTION myDateRangePF1 (int)
      AS RANGE LEFT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020) ;
    GO

    -- S Creates a partition scheme called myDateRangePS1 that applies myDateRangePF1 to the six filegroups created above
    CREATE PARTITION SCHEME myDateRangePS1 --S
      AS PARTITION myDateRangePF1 --F
      TO (sw2015fg, sw2016fg, sw2017fg, sw2018fg, sw2019fg, sw2020fg) ;
    GO

    -- Create partitioned tables called BI_ that uses myDateRangePS1 to partition actvt_ym
    CREATE TABLE [dbo].[BI_Actvt](
    [actvt_ym] [int] null,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Rev](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    CREATE TABLE [dbo].[BI_Usg](
    [actvt_ym] [int] NULL,
    [actvt_dt] [date] NULL,
    ...
    ) ON myDateRangePS1 (actvt_ym)
    GO

    Your partition function has 6 so you have 7 partitions. It's based on covering everything less than and greater than what you specify in the function. Some things you read might say something like "create an extra partition". But it's really about covering everything and understanding whether to designate right or left range rather than just slapping another partition in there. This is an old post but still good - it's pretty easy to see with the partitions in their example and it explains the range right/left and how that fits in:
    Table Partitioning Basics

    Sue

    Thanks, this helped a lot. I now have other issues, but its separate from this so will make a new post.

    Glad it helped - thanks for posting back!
    You'll get through all of these. It's one of those...different issues working through it all and then it comes together and makes sense.

    Sue

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply