A simple framework for implementing a time-framed record deletion policy: deleting records that are older than a certain threshold.
Consists of a logging table and a stored procedure which deletes the records. The procedure deletes rows in batches and provides for an overall maximum to be specified for a single execution to limit impact on resources when there is a huge set of records to delete.
Logging Table
CREATE TABLE [dbo].[RecordDeletions]
(
[RecordDeletionId] INT NOT NULL IDENTITY,
[TableName] VARCHAR(50) NULL,
[DeletionThresholdDate] smalldatetime NULL,
[OperationStatus] VARCHAR(50) NOT NULL CONSTRAINT DF_RecordDeletions_OperationStatus DEFAULT 'Running',
[StartTime] DATETIME NOT NULL CONSTRAINT DF_RecordDeletions_StartTime DEFAULT GetDate(),
[EndTime] DATETIME NULL,
[NbrRecordsDeleted] INT NULL ,
[DurationSeconds] as DATEDIFF(SECOND, StartTime, EndTime),
CONSTRAINT [PK_RecordDeletions] PRIMARY KEY ([RecordDeletionId]),
CONSTRAINT [CK_RecordDeletions_OperationStatus_Valid_Values]
CHECK (OperationStatus = 'Running' OR OperationStatus = 'Success' OR OperationStatus = 'Failure')
)
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'Keeps a log of records that have been deleted from tables for housekeeping',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = NULL,
@level2name = NULL
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'Time that this operation started',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = N'COLUMN',
@level2name = N'StartTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'Time that this operation ended',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = N'COLUMN',
@level2name = N'EndTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'Number of records deleted',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = N'COLUMN',
@level2name = N'NbrRecordsDeleted'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'Name of table',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = N'COLUMN',
@level2name = N'TableName'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'Status of operation: Running, Success, Failure',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = N'COLUMN',
@level2name = N'OperationStatus'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'(Computed column) Seconds duration of completed events.',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = N'COLUMN',
@level2name = N'DurationSeconds'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'Rows older than the DeletionThresholdDate are targetted for deletion in this event.',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'RecordDeletions',
@level2type = N'COLUMN',
@level2name = N'DeletionThresholdDate'
Record Deletion Procedure
This procedure is fairly basic and requires duplication and customisation for each table (or cluster of related tables). Search and replace TARGET_TABLE_NAME – but the appropriate Primary Key and Date columns will also have to be edited in the “Deletion Candidates Selection Block”.
CREATE PROCEDURE dbo.DeleteOldRows_TARGET_TABLE_NAME
/*
Delete rows older than the retention period
@DeleteBatchSize : number of records deleted in each loop.
@OverallMaxRowsPerExecution : absolute max number of rows that will be deleted
in a single execution of the procedure.
*/
AS
SET NOCOUNT ON;
SET XACT_ABORT ON
BEGIN TRY
DECLARE @DeleteBatchSize int = 5000
DECLARE @OverallMaxRowsPerExecution int = 400000
DECLARE @DebugInfo varchar(2000)
DECLARE @MaxRowNumber int = 0
DECLARE @RangeStart int
DECLARE @RangeEnd int
DECLARE @RecordDeletionId int
DECLARE @NbrRecordsDeleted int = 0
DECLARE @OperationStatus varchar(50) = 'Failure'
CREATE TABLE #RecordsToDelete
(RowNumber int not null PRIMARY KEY IDENTITY,
RecordId int not null
)
-- Insert logging row, calculate time threshold.
INSERT dbo.RecordDeletions
(TableName,
DeletionThresholdDate)
VALUES
('TARGET_TABLE_NAME',
DATEADD(MONTH, (-60), CAST(GETDATE() AS date);
SET @RecordDeletionId = SCOPE_IDENTITY()
-- Deletion Candidates Selection Block
INSERT INTO #RecordsToDelete (RecordId)
SELECT TOP (@OverallMaxRowsPerExecution)
ID
FROM
dbo.TARGET_TABLE_NAME L
INNER JOIN dbo.RecordDeletions RD
ON L.RowCreatedDate <= RD.DeletionThresholdDate
WHERE
RD.RecordDeletionId = @RecordDeletionId
SELECT
@RangeStart = MIN(RowNumber),
@MaxRowNumber = MAX(RowNumber)
FROM
#RecordsToDelete
WHILE (@RangeStart < @MaxRowNumber)
BEGIN
SET @RangeEnd = (@RangeStart + @DeleteBatchSize -1)
DELETE TargetTable
FROM
dbo.TARGET_TABLE_NAME TargetTable
INNER JOIN #RecordsToDelete R ON TargetTable.ID = R.RecordId
WHERE
R.RowNumber BETWEEN @RangeStart and @RangeEnd
SET @NbrRecordsDeleted += @@ROWCOUNT
SET @RangeStart += @DeleteBatchSize
WAITFOR DELAY '000:00:00.500' -- pause for 500ms to allow other processes to gain locks.
END
SET @OperationStatus = 'Success'
END TRY
BEGIN CATCH
EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
SET @OperationStatus = 'Failure';
END CATCH
BEGIN TRY
SET @DebugInfo = 'Logging and Cleanup section'
UPDATE
dbo.RecordDeletions
SET
OperationStatus = @OperationStatus,
EndTime = GETDATE(),
NbrRecordsDeleted = @NbrRecordsDeleted
WHERE
RecordDeletionId = @RecordDeletionId
DROP TABLE #RecordsToDelete
END TRY
BEGIN CATCH
EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
RETURN 5555
END CATCH
Uses the Error Handler for T-SQL.
