Extend Database Partition with multiple filegroups

KamalPresna Boopathy 21 Reputation points
2021-07-20T21:48:25.713+00:00

I'm currently working on extending database partition to a few more partitions. For instance we currently have FG1 [File Group], FG2, FG3, FG4, FG5 and planning to add FG6, FG7, FG8, FG9. Will there be a problem, if execute the below all at once?. My confusion is on How NEXT USED works? As per explanation "In a partition scheme, only one filegroup can be designated NEXT USED" but in the below we gave FG6, FG7, FG8 as NEXT USED. If this is a problem, How to get around it?

ALTER PARTITION SCHEME {PartitionScheme} NEXT USED FG6 GO
ALTER PARTITION FUNCTION {PartitionFunction}() SPLIT RANGE(5) GO

ALTER PARTITION SCHEME {PartitionScheme} NEXT USED FG7 GO
ALTER PARTITION FUNCTION {PartitionFunction}() SPLIT RANGE(6) GO

ALTER PARTITION SCHEME {PartitionScheme} NEXT USED FG8 GO
ALTER PARTITION FUNCTION {PartitionFunction}() SPLIT RANGE(7) GO

and so on....

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,959 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,652 questions
{count} votes

Accepted answer
  1. Dan Guzman 9,236 Reputation points
    2021-07-20T22:15:01.25+00:00

    The NEXT USED filegroup of each partition scheme determines the filegroup the new partition created on when the function is SPLIT. You'll need to set a NEXT USED filegroup for scheme(s) before splitting the function unless the next used has already been set.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.