0

I'm trying to create a job that executed automatically the rotation of partitions on a table.

I executed the next script but the ranges are not rotating successfully

DECLARE @PARTITION_FUNCATION_NAME VARCHAR(50) = 'pfPerformanceSummary';

DECLARE @NewBounderyDay DATETIME,
        @MinBounderyDay DATETIME,
        @PartitionFunctionId BIGINT,
        @PartitionIdToDelete INT,
        @FileGroupName VARCHAR(50);

SELECT @PartitionFunctionId = function_id 
FROM sys.partition_functions
WHERE [name] = @PARTITION_FUNCATION_NAME;

SELECT 
    @MinBounderyDay = CONVERT(DATETIME,MIN([value])), 
    @NewBounderyDay = DATEADD(HOUR,1,CONVERT(DATETIME,MAX([value]))),
    @PartitionIdToDelete = MIN(boundary_id)
FROM sys.partition_range_values pr
LEFT JOIN sys.partition_functions pf ON pf.function_id = pr.function_id 
WHERE pr.function_id = @PartitionFunctionId
AND boundary_id>1;

TRUNCATE TABLE PerformanceSummary WITH (PARTITIONS (@PartitionIdToDelete));

ALTER PARTITION FUNCTION pfPerformanceSummary() MERGE RANGE (@MinBounderyDay);

ALTER PARTITION SCHEME psPerformanceSummary NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION pfPerformanceSummary() SPLIT RANGE (@NewBounderyDay); 

Before executed the script the table looks enter image description here

But after executed looks enter image description here

Can you help about what is wrong in the sentence, please?

1
  • 2
    AND boundary_id>1 why is that there, given that it's a RIGHT range? Don't you want to merge the lowest partition rather than second lowest? Side note: The LEFT JOIN seems extraneous. Commented Jan 31 at 0:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.