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.