This stored procedure helps automatically create new partition on a new file group to an already partitioned table. I have tested the script in my testlab. Please review and let me know for any corrections if required.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Sherbaz Mohamed -- Create date: 22 September 2014 -- Description: This SP will create a new partition whenever it is executed. -- Also the table should have been already partitioned and -- needs to have atleast 2 boundary values and 3 filegroups. -- Example: sp_sherPart 'sherbaz', 'dbo', 'table1' -- ============================================= CREATE PROCEDURE sp_sherPart -- Add the parameters for the stored procedure here @databasename varchar(50) = '[testdb]', @schemaname varchar(50) = 'dbo', @tablename varchar(50) = 'table1', @newFileLocation varchar(200) = null AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- The script begins DECLARE @partitionFunction varchar(50), @partitionScheme varchar(50), @oldFilePath varchar(200) if object_id('tempdb..#partitionLayout') is not null drop table #partitionLayout -- Read the partition layout to identify the Boundary values to create the new partition. SELECT N'DatabaseName' = @databasename , N'SchemaName' = s.name , N'TableName' = o.name , N'IndexName' = i.name -- , N'IndexType' = i.type_desc , N'PartitionScheme' = ps.name -- , N'DataSpaceName' = ds.name -- , N'DataSpaceType' = ds.type_desc , N'PartitionFunction' = pf.name , N'PartitionNumber' = dds.destination_id , N'BoundaryValue' = prv.value , N'RightBoundary' = pf.boundary_value_on_right -- 1 = TRUE (values are less than boundary) -- 0 = FALSE (values are less than or equal to boundary) , N'PartitionFileGroup' = ds2.name , N'RowsOfData' = p.[rows] into #partitionLayout FROM sys.objects AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id] INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id AND p.partition_number = dds.destination_id LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id WHERE s.name = @schemaname -- schema name AND o.name = @tablename -- table name ORDER BY DatabaseName , SchemaName , TableName , IndexName , PartitionNumber select @partitionFunction = PartitionFunction, @partitionScheme = PartitionScheme from #partitionLayout select @partitionFunction as partitionFunction, @partitionScheme as partitionScheme; declare @boundaryValues TABLE(IDs int, ID INT IDENTITY(1,1)); declare @lowerBoundary int, @upperBoundary int, @addPartition varchar(200); insert into @boundaryValues(IDs) select convert(int, Boundaryvalue) from #partitionLayout where BoundaryValue is not NULL order by BoundaryValue desc select @lowerBoundary=IDs from @boundaryValues where ID =2 select @upperBoundary=IDs from @boundaryValues where ID =1 select @lowerBoundary as lowerBoundary, @upperBoundary as upperBoundary -- Create the Alter Partition statement from the boundary values detected. set @addPartition = 'ALTER PARTITION FUNCTION ' + @partitionFunction + ' () SPLIT RANGE ('+ convert(varchar(100),@upperBoundary+(@upperBoundary-@lowerBoundary)) +');' declare @filegroupName varchar(50), @filegroupCount int, @addFilegroup varchar(300) -- Read existing filegroup name and count the number of existing filegroups. select top 1 @filegroupName = name from sys.filegroups order by data_space_id desc select @filegroupCount = count(name) from sys.filegroups select @filegroupName as filegroupName, @filegroupCount as filegroupCount -- Fetch the data file location from existing data files and assign it for the new one if required. if @newFileLocation is null begin SELECT TOP 1 @oldFilePath=physical_name FROM sys.database_files WHERE name=@databasename; select @newFileLocation=rtrim(right(@oldFilePath, charindex('\', reverse(@oldFilePath)) -1)) set @newFileLocation = replace(@oldFilePath,@newFileLocation,'') select @newFileLocation as newFileLocation end -- Create statments to add the new filegroup and datafile. set @addFilegroup = 'ALTER DATABASE ['+@databasename+'] ADD FILEGROUP ['+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+'] ALTER DATABASE ['+@databasename+'] ADD FILE ( NAME = N'''+@databasename+'_'+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+''', FILENAME = N'''+@newFileLocation+'\'+@databasename+'_'+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+'.ndf'') TO FILEGROUP ['+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+']' print @addFileGroup EXECUTE(@addFileGroup) -- Create statements to tell SQL about the NEXT USED filgroup information. declare @alterPartitionScheme varchar(200) set @alterPartitionScheme = 'ALTER PARTITION SCHEME '+@partitionScheme+' NEXT USED ['+SUBSTRING(@filegroupName,1,3)+convert(varchar(2),(@filegroupCount+1))+'];' PRINT @alterPartitionScheme EXECUTE(@alterPartitionScheme) print @addPartition EXECUTE(@addPartition) END GO |
Continue reading “Automatically add Partition for Partitioned Tables”