Split a partition File Group

Some file groups that store partitions are too large. You 'd better split them.

1. First, find the partition number corresponding to the file group to determine the new boundary value to be set based on the Partition Number.

2. Add new file groups and files

3. Set the new file group to next used.

4. Modify the Partition Function and split the partition.

Detailed steps:

1. Locate the Partition Number

Use [dB] Go -- select FG for the file group to be split. name, SP. destination_id from sys. destination_data_spaces SP inner join sys. partition_schemes SCM on sp. partition_scheme_id = SCM. data_space_id inner join sys. filegroups FG on FG. data_space_id = sp. data_space_id where SCM. name = 'partition scheme' and Fg. name = 'logical name of the file group ';

2. Set the new boundary value

For example, if the Partition Number is 6 and corresponds to the partition function (assuming that the partition function is range left for values), the new boundary value should fall between 5th and 6th boundary values, creates a new 6th boundary value.

Generally, a partition function has many boundary values. If it depends on a number, how can it be counted if the Partition Number is dozens or even hundreds. You can use the following statement to search:

Select min (partition by column), max (partition by column) from [Table name] where $ partition. Partition Function (partition by column) = Partition Number

However, the records in the table have become uneven after various query, deletion, and modification operations. The preceding statements may not be able to get numbers. You can also use the following code:

Declare @ offset Int = 10000; -- step size, depending on the actual situation declare @ limit Int = maximum boundary value; declare @ partitionid Int = Partition Number; declare @ min Int = 0, @ Max Int = 0; declare @ I Int = 0; while @ I <= @ limitbegin if $ partition. partition Function (@ I) = @ partitionid and @ min = 0 set @ min = @ I; if $ partition. partition Function (@ I) = @ partitionid set @ max = @ I; Set @ I = @ I + @ offset; endselect @ min, @ Max;

3. Add new file groups and files

-- File group alter database [database name] add filegroup [new file group name] Go -- file alter database [database name] add file (name = n' new filename ', filename = n'file full path', size = 3 MB, filegrowth = 1 MB) to filegroup [new file group name] Go

Note: If the partition function corresponds to multiple partition schemes, you should generally add multiple file groups and files accordingly.

4. Modify the partition scheme and set the new file group to next used.

Alter Partition Scheme [partition solution name] Next used [new file group name]; go

Similarly, if the partition function corresponds to multiple partition schemes, each partition scheme should be set

5. Modify partition functions

Alter partition function () split range (new boundary value );

After the preceding operations are completed, you can re-open the Partition Function and the partition solution. For the range left for values partition function, the new file group is used to save the data on the left side of the new boundary value, while the data on the right side is saved by the original file group.