על הכיפק, תודה :)
הוספתי להנאתכם את הפרוצדורה המרוכזת שמפצלת את כל ה-partition functions.
הפרוצדורה מניחה כמובן שכל ה-partition functions/schemes קיימות, ושמם נמצא בתוך טבלת הניהול.
עשיתי לזה קצת Debugging ועושה רושם שזה עובד :)
שבת שלום !
CREATE TABLE PartitionManagement
( PartitionManagementID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TableName VARCHAR(20) NOT NULL,
PartitionFunction VARCHAR(30) NOT NULL,
PartitionScheme VARCHAR(30) NOT NULL,
PartitionIntervalType VARCHAR(10) NOT NULL,
PartitionsAhead INT NOT NULL,
)
INSERT INTO PartitionManagement (TableName ,PartitionFunction ,PartitionScheme ,PartitionIntervalType,PartitionsAhead )
VALUES ('TRILILI_XDIV', 'pf_TRILILI_XDIV', 'ps_TRILILI_XDIV', 'Year', 5),
('TRILILI_XDMU', 'pf_TRILILI_XDMU', 'ps_TRILILI_XDMU', 'Year', 5),
('TRILILI_XDPO', 'pf_TRILILI_XDPO', 'ps_TRILILI_XDPO', 'Month', 12),
('TRILILI_XEMU', 'pf_TRILILI_XEMU', 'ps_TRILILI_XEMU', 'Month', 12),
('TRILILI_XEPO', 'pf_TRILILI_XEPO', 'ps_TRILILI_XEPO', 'Month', 12),
('TRILILI_XFMU', 'pf_TRILILI_XFMU', 'ps_TRILILI_XFMU', 'Month', 12),
('TRILILI_XFPO', 'pf_TRILILI_XFPO', 'ps_TRILILI_XFPO', 'Month', 12),
('TRILILI_XRSK', 'pf_TRILILI_XRSK', 'ps_TRILILI_XRSK', 'Month', 12),
GO
-- =============================================
-- Author: Roni Vered
-- Create date: 2012-10-18
-- Description: split partition FUNCTIONS
-- Job name PRD - Partitions - SPLIT_AddPartition
-- =============================================
ALTER PROCEDURE [dbo].[usp_Partition_SPLIT_AddPartitions]
AS
declare @d datetime
declare @MaxDate datetime
declare @NowDateTime datetime
declare @ServerTTL smallint
declare @PartitionFunctionID int
declare @PartitionInterval int
--Variables for PartitionManagement table info
declare @TableName varchar(20),
@PartitionFunction varchar(30),
@PartitionScheme varchar(30),
@PartitionIntervalType varchar(10) ,
@PartitionsAhead int
--The partition interval
set @PartitionInterval=1
DECLARE db_cursor CURSOR FOR
SELECT TableName, PartitionFunction, PartitionScheme, PartitionIntervalType, PartitionsAhead
FROM PartitionManagement
ORDER BY TableName
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TableName, @PartitionFunction, @PartitionScheme, @PartitionIntervalType, @PartitionsAhead
WHILE @@FETCH_STATUS = 0
BEGIN
--Finding the partition function of the table @TableName
select @PartitionFunctionID= function_id from sys.partition_functions where name=@PartitionFunction
--Amount of partitions adhead
set @ServerTTL=@PartitionsAhead
--The Max datetime of existing partitions
if @PartitionIntervalType='YEAR'
set @MaxDate=dateadd (YEAR ,@ServerTTL,getdate())
else if @PartitionIntervalType='MONTH'
set @MaxDate=dateadd (month,@ServerTTL,getdate())
--The Current truncated datetime
set @NowDateTime=cast (cast(getDate() As Date) as datetime)
--Checking what is the latest existing partition date range
select top 1 @d= cast(value as datetime) from sys.partition_range_values
where function_id=@PartitionFunctionID and cast(value as datetime) <> '2020-01-01 00:00:00'
order by value desc
/*
select top 1 cast(value as datetime) from sys.partition_range_values
where function_id=65556 and cast(value as datetime) <> '2020-01-01 00:00:00'
order by value desc
*/
--Advancing @d 1 month/year ahead. (we cannot user variables in the datepart of DATEADD function, hence the IF condition is used)
if @PartitionIntervalType='YEAR'
set @d=dateadd(YEAR,@PartitionInterval,@d)
else if @PartitionIntervalType='MONTH'
set @d=dateadd(MONTH,@PartitionInterval,@d)
/* --Debugging purposes.
print '--------------------------------------------'
print '@TableName= '+@TableName
print '@d= '+convert (varchar(20),@d,120)
print '@MaxDate= '+convert (varchar(20),@d,120)
print '@PartitionIntervalType= '+@PartitionIntervalType
print '@PartitionsAhead= '+cast ( @PartitionsAhead as varchar(10))
*/
--@d<=@MaxDate then we need to create more partitions
while @d <= @MaxDate
begin
SET LOCK_TIMEOUT 10000
DECLARE @sql VARCHAR(256) = 'ALTER PARTITION SCHEME '+@PartitionScheme+' NEXT USED [PRIMARY]'
--print (@sql)
EXEC (@sql)
SET @sql = 'ALTER PARTITION FUNCTION '+@PartitionFunction+'() split range ('''+convert (varchar(20),@d,120) +''')'
--print (@sql)
EXEC (@sql)
if @PartitionIntervalType='YEAR'
set @d=dateadd(year,1,@d)
else if @PartitionIntervalType='MONTH'
set @d=dateadd(month,1,@d)
end
if @PartitionIntervalType='YEAR'
set @MaxDate= dateadd(YEAR,-1*@PartitionInterval,@NowDateTime)
else if @PartitionIntervalType='MONTH'
set @MaxDate= dateadd(MONTH,-1*@PartitionInterval,@NowDateTime)
FETCH NEXT FROM db_cursor INTO @TableName, @PartitionFunction, @PartitionScheme, @PartitionIntervalType, @PartitionsAhead
END
CLOSE db_cursor
DEALLOCATE db_cursor
return 0
GO
