|
CREATE PROC dbo.SlideRangeRightWindow_datetime
@RetentionDays int,
@RunDate datetime = NULL
/*
This proc maintains a RANGE RIGHT daily sliding window
based on the specified @RetentionDays. In addition to
purging old data, the partition function is adjusted to
account for scheduling issues or changes in @RetentionDays.
Partitions are split and merged so that the first partition
contains data older than the specifed retention period and
the last two partitions (future data) are empty. The other
petitions will contain historical and current data for the
specified number of @RetentionDays.
After successful execution, (at least) the following
partitions will exist:
- partition 1 = data older than retained date (empty)
- third from last partition = current data (@RunDate)
- second from last partition = @RunDate + 1 (normally empty)
- last partition = @RunDate + 2 (normally empty)
*/
AS
SET NOCOUNT, XACT_ABORT ON;
DECLARE
@Error int,
@RowCount bigint,
@ErrorLine int,
@Message varchar(255),
@ExpirationDate datetime,
@PartitionBoundaryDate datetime;
SET @Error = 0;
BEGIN TRY
IF @RunDate IS NULL
BEGIN
--use current date (midnight) if no date specified
SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', GETDATE()));
END
ELSE
BEGIN
--set time to midnight of specified date
SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', @RunDate));
END
SET @Message =
'Run date = ' +
CONVERT(varchar(23), @RunDate, 121) +
', Retention days = ' +
CAST(@RetentionDays AS varchar(10));
RAISERROR (@Message, 0, 1) WITH NOWAIT;
--calculate expiration date based on @RetentionDays and @RunDate
SET @ExpirationDate = DATEADD(day, @RetentionDays * -1, @RunDate);
RAISERROR ('Partition boundaries before maintenance:', 0, 1) WITH NOWAIT;
SELECT CAST(prv.value AS datetime) AS PartitionBoundary
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE pf.name = 'PF_MyPartitionFunction';
--get oldest existing boundary
SELECT
@PartitionBoundaryDate =
CAST(MIN(prv.value) AS datetime)
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE pf.name = 'PF_MyPartitionFunction';
BEGIN TRAN;
--acquire exclusive table lock to prevent deadlocking
--with concurrent activity.
SELECT TOP 1 @error = 0
FROM dbo.MyPartitionedTable WITH (TABLOCKX, HOLDLOCK);
--If first partition boundary is later than expiration date,
--split first partition so that it contains only expired data.
IF @PartitionBoundaryDate > @ExpirationDate
OR @PartitionBoundaryDate IS NULL
BEGIN
SET @PartitionBoundaryDate = @ExpirationDate;
ALTER PARTITION SCHEME PS_MyPartitionScheme
NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF_MyPartitionFunction()
SPLIT RANGE(@PartitionBoundaryDate);
SET @Message =
'Created boundary ' +
CONVERT(varchar(30), @PartitionBoundaryDate, 121);
RAISERROR(@Message, 0, 1) WITH NOWAIT;
END;
--make sure we have a boundary for oldest period to be retained
IF NOT EXISTS
(
SELECT prv.value
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE
pf.name = 'PF_MyPartitionFunction'
AND CAST(prv.value AS datetime) = DATEADD(day, 1, @ExpirationDate)
)
BEGIN
ALTER PARTITION SCHEME PS_MyPartitionScheme
NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF_MyPartitionFunction()
SPLIT RANGE(DATEADD(day, 1, @ExpirationDate));
SET @Message =
'Created boundary ' +
CONVERT(varchar(30), DATEADD(day, 1, @ExpirationDate), 121);
RAISERROR(@Message, 0, 1) WITH NOWAIT;
END;
--Switch and merge partitions older that retention period
WHILE @PartitionBoundaryDate <= @ExpirationDate
BEGIN
--Ensure target staging partition is empty
TRUNCATE TABLE dbo.MyPartitionedTable_Staging;
ALTER TABLE dbo.MyPartitionedTable SWITCH PARTITION $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate) - 1
TO dbo.MyPartitionedTable_Staging PARTITION $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate) - 1;
--get count of purged rows for message
SELECT @RowCount = rows
FROM sys.partitions
WHERE
object_id = OBJECT_ID(N'MyPartitionedTable_Staging')
AND partition_number = $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate) - 1;
--purge data permanently
TRUNCATE TABLE dbo.MyPartitionedTable_Staging;
SET @Message =
'Moved data older than ' +
CONVERT(varchar(23), @PartitionBoundaryDate, 121) +
' (' + CAST(@RowCount as varchar(20)) + ' rows) to staging table';
RAISERROR(@Message, 0, 1) WITH NOWAIT;
IF $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate) > 1
BEGIN
--merge first and second partitions
ALTER PARTITION FUNCTION PF_MyPartitionFunction()
MERGE RANGE(@PartitionBoundaryDate);
SET @Message =
'Removed boundary ' +
CONVERT(varchar(30), @PartitionBoundaryDate, 121);
RAISERROR(@Message, 0, 1) WITH NOWAIT;
ENd
--get oldest partition boundary for next iteration
SET @PartitionBoundaryDate = NULL;
SELECT
@PartitionBoundaryDate =
CAST(MIN(prv.value) AS datetime)
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE pf.name = 'PF_MyPartitionFunction';
END;
--Make sure we have individual partitons for tomorrow
--and future days.
SET @PartitionBoundaryDate = DATEADD(day, 1, @RunDate);
WHILE @PartitionBoundaryDate <= DATEADD(day, 2, @RunDate)
BEGIN
IF NOT EXISTS
(
SELECT prv.value
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE
pf.name = 'PF_MyPartitionFunction'
AND CAST(prv.value AS datetime) = @PartitionBoundaryDate
)
BEGIN
ALTER PARTITION SCHEME PS_MyPartitionScheme
NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF_MyPartitionFunction()
SPLIT RANGE(@PartitionBoundaryDate);
SET @Message =
'Created boundary ' +
CONVERT(varchar(30), @PartitionBoundaryDate, 121);
RAISERROR(@Message, 0, 1) WITH NOWAIT;
END;
ELSE
BEGIN
SET @Message =
'Partition already exists for boundary ' +
CONVERT(varchar(30), @PartitionBoundaryDate, 121);
RAISERROR(@Message, 0, 1) WITH NOWAIT;
END;
--calc boundary date for next iteration
SET @PartitionBoundaryDate =
DATEADD(day, 1, @PartitionBoundaryDate);
END
COMMIT;
END TRY
BEGIN CATCH
SELECT
@Error = ERROR_NUMBER(),
@Message = ERROR_MESSAGE(),
@ErrorLine = ERROR_LINE();
SET @Message =
'Partition maintenenace failed with error %d at line %d: ' +
@Message;
RAISERROR(@Message, 16, 1, @Error, @ErrorLine) WITH NOWAIT;
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END;
END CATCH;
RAISERROR ('Partition boundaries after maintenance', 0, 1) WITH NOWAIT;
SELECT CAST(prv.value AS datetime) AS PartitionBoundary
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON
prv.function_id = pf.function_id
WHERE pf.name = 'PF_MyPartitionFunction';
Done:
RETURN @error;
GO
|