The following is a script for adding a new filegroup and partition to an already partitioned table. What it basically does is to find the partitioning factor based on the distribution of data between partition number 1 and 2 and then add that to the largest partition. Example
Partition 1: less then 10
Partition 2: less than 20
...
Partition 8: less than 80
Partition 9: everything else
Partitioning factor = 10 since 20 - 10 = 10. Then it finds the latest partition (80) and adds a partition with a range of 90 (80 +10) to that.
There are some prerequisites for the script to function:
- The script only supports tables partitioned by an int column
- Each partition must have it's own filegroup
- The table must have at least 3 active partitions; 2 range partitions and the "catch-all" partition
- Partitions/files/filegroups must have matching naming convention and have a 3 digit number at the end (i.e FG001, FG002, etc)
- Filegroup datafilename must be in the form FG004_data where the "FG"-part can be whatever
I'm aware that these prerequisites probably disqualifies the script for general public use but maybe you can make it to fit your needs...I made it to fit my own needs primarily.CREATE PROCEDURE dbo.AddPartitions
@TableName varchar(200)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@OldFGName varchar(50),
@NewFGName varchar(50),
@OldFileName varchar(400),
@NewFileName varchar(400),
@FGPrefix varchar(400),
@RangeFactor int,
@NewRange int,
@SQL nvarchar(2000)
DECLARE @PartitionInfo table (
Object_name varchar(200),
Index_name varchar(200),
Partition_number int,
Filegroup_name varchar(200),
Value bigint,
SchemeName varchar(200),
FunctionName varchar(200)
)
INSERT INTO @PartitionInfo
SELECT
OBJECT_NAME(i.object_id),
i.name,
p.partition_number,
fg.name,
CAST(value AS bigint),
ps.name,
f.name
FROM sys.partitions p
JOIN sys.indexes i
ON p.object_id = i.object_id
and p.index_id = i.index_id
JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f
ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
WHERE i.index_id < 2
AND OBJECT_NAME(i.object_id) = @TableName
SET @OldFGName = (SELECT TOP 1 FILEGROUP_NAME FROM @PartitionInfo ORDER BY Value DESC)
SET @FGPrefix = REPLACE(@OldFGName, RIGHT(@OldFGName, 3), '')
SET @NewFGName = @FGPrefix + RIGHT('000' + CAST(CAST(RIGHT(@OldFGName, 3) AS int) + 1 AS varchar(50)), 3)
SET @SQL = 'ALTER DATABASE ' + db_name() + ' ADD FILEGROUP ' + @NewFGName + CHAR(13)
SET @OldFileName = (SELECT physical_name FROM sys.database_files WHERE name = @OldFGName + '_data')
SET @NewFileName = REPLACE(@OldFileName, @OldFGName, @NewFGName)
SET @SQL = @SQL + 'ALTER DATABASE ' + db_name() + ' ADD FILE (' +
'NAME = ' + @NewFGName + '_data, ' +
'FILENAME = ''' + @NewFileName + ''', ' +
'SIZE = 100MB, ' +
'FILEGROWTH = 10%) ' +
'TO FILEGROUP ' + @NewFGName + CHAR(13)
SET @SQL = @SQL + 'ALTER PARTITION SCHEME ' + (SELECT TOP 1 SchemeName FROM @PartitionInfo) + ' NEXT USED ' + @NewFGName + CHAR(13)
SET @RangeFactor = (SELECT CAST(b.value AS int) - CAST(a.value AS int)
FROM @PartitionInfo a
CROSS JOIN @PartitionInfo b
WHERE a.partition_number = 1
and b.partition_number = 2)
SET @NewRange = (SELECT @RangeFactor + MAX(CAST(value as int)) FROM @PartitionInfo WHERE value IS NOT NULL)
SET @SQL = @SQL + 'ALTER PARTITION FUNCTION ' + (SELECT TOP 1 FunctionName FROM @PartitionInfo) + '() SPLIT RANGE (' + CAST(@NewRange as varchar(50)) + ')'
--EXEC sp_executesql @SQL
PRINT @sql
END
- Lumbago
http://xkcd.com/327/
Create a new partition in a new filegroup
Posted - 12/10/2009 : 11:12:41
