Create a new partition in a new filegroup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Create a new partition in a new filegroup

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-12-10 : 11:12:41

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/

sujha
Starting Member

1 Post

Posted - 2010-01-05 : 05:34:42

Adding a partition to a partitioned table is an important partitioning maintenance activity. You must add a new partition periodically depending on your partitioning needs. For example, our partitioned Orders table partitions the data by year, so every year you need to add a new partition. You add a new partition by splitting an existing partition-typically the last. The last partition covers the range - 8 (infinity). In our case, the last partition covers the range >= 2004. To create a new partition that covers the range >= 2005, split the last existing partition by altering the partitioning function and specifying the new boundary point: 20050101. After the operation, the original last partition is split into two partitions: one covering the range >= 2004 and < 2005, the other covering the range >= 2005. . . .

[url]www.Discountsvu.com[/url]

Price Comparison

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources