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.
Extend Database Partition with multiple filegroups
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....
-
Dan Guzman 9,236 Reputation points
2021-07-20T22:15:01.25+00:00