SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Create a new partition in a new filegroup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 12/10/2009 :  11:12:41  Show Profile  Reply with Quote
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/

Edited by - Lumbago on 12/10/2009 11:16:28

sujha
Starting Member

India
1 Posts

Posted - 01/05/2010 :  05:34:42  Show Profile  Reply with Quote
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. . . .

www.Discountsvu.com

Price Comparison
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000