|
CREATE PROC dbo.SlideRangeLeftWindow_datetime
@RetentionDays int,
@RunDate datetime = NULL
/*
This proc maintains a RANGE LEFT 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 the oldest data 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 = oldest data period
- 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;
--set datetime boundary to latest posible datetime of previous day
SET @RunDate = DATEADD(millisecond, -3, @RunDate);
--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;
--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 1
TO dbo.MyPartitionedTable_Staging PARTITION 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 = 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;
--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;
--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
|