HbXXv3Yv

1/*
2* Ensure the columns you are basing your WHERE clause criteria are defined in an appropriate index or two - e.g. [CreateDate] or [StatusId] (which should have indexes anyhow).
3
4* Fetch the TOP (N) Primary Key of the records to be deleted into a temp table.
5
6* Utilize that temp table to JOIN onto - EXISTS or IN are also appropriate, but JOIN can be more efficient in certain scenarios such as deleting records from descendants.
7
8*/
9
10-- SQL Server 2019
11
12SET NOCOUNT ON;
13SET XACT_ABORT ON;
14SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
15
16-- Set the amount of records to be purged in an iteration.
17DECLARE @PurgeCount BIGINT = 1000000;
18
19-- Set the target retention period.
20DECLARE @PurgeCreateDate DATE = DATEADD(MONTH, -3, SYSUTCDATETIME());
21
22-- Set the target status.
23DECLARE @PurgeStatusId SMALLINT = 1; -- "Complete"
24
25-- Ensure a record exists to be purged to cleanly exit the WHILE LOOP once complete.
26DECLARE @IsPurgeTrue BIT = 1;
27
28-- Loop through the records to be purged.
29WHILE (@IsPurgeTrue = 1) 
30BEGIN
31    DROP TABLE IF EXISTS [#RecordToPurge];
32
33    -- Create temporary table to store the records to be purged.
34    CREATE TABLE [#RecordToPurge] (
35        [RecordId] BIGINT NOT NULL
36    );
37
38    CREATE CLUSTERED INDEX [PK_RecordId] ON [#RecordToPurge] ([RecordId]);
39
40    -- Fetch the records to be purged.
41    INSERT INTO [#RecordToPurge] (
42        [RecordId]
43    )
44    SELECT TOP (@PurgeCount)
45        [ttp].[RecordId]
46    FROM
47        [DatabaseToPurge].[dbo].[TableToPurge] [ttp]
48    WHERE
49        [ttp].[CreateDate] > @PurgeCreateDate
50        AND [ttp].[StatusId] = @PurgeStatusId
51    ORDER BY
52        [ttp].[RecordId];
53
54-- Begin the purge operation within a TRY/CATCH block.
55BEGIN TRY
56BEGIN TRANSACTION;
57IF EXISTS (SELECT TOP (1) 1 FROM [#RecordToPurge])
58    BEGIN
59        -- Purge records from descendants with Foreign Keys.
60        DELETE
61            [ttp_x]
62        FROM
63            [DatabaseToPurge].[dbo].[TableToPurgeDetail_X] [ttp_x]
64            JOIN [#RecordToPurge] [rtp]
65                ON [ttp_x].[RecordId] = [rtp].[RecordId];
66
67        DELETE
68            [ttp_y]
69        FROM
70            [DatabaseToPurge].[dbo].[TableToPurgeDetail_Y] [ttp_y]
71            JOIN [#RecordToPurge] [rtp]
72                ON [ttp_y].[RecordId] = [rtp].[RecordId];
73
74        DELETE
75            [ttp_z]
76        FROM
77            [DatabaseToPurge].[dbo].[TableToPurgeDetail_Y] [ttp_z]
78            JOIN [#RecordToPurge] [rtp]
79                ON [ttp_z].[RecordId] = [rtp].[RecordId];
80
81        -- Purge records from parent.
82        DELETE
83            [ttp]
84        FROM
85            [DatabaseToPurge].[dbo].[TableToPurgeDetail_Y] [ttp]
86            JOIN [#RecordToPurge] [rtp]
87                ON [ttp].[RecordId] = [rtp].[RecordId];
88
89        SET @IsPurgeTrue = 1;
90    END;
91ELSE
92    BEGIN
93        -- Exit the loop once all records have been purged.
94        DROP TABLE IF EXISTS [#RecordToPurge];
95
96        SET @IsPurgeTrue = 0;
97    END;
98COMMIT TRANSACTION;
99END TRY
100BEGIN CATCH
101    -- Test whether the transaction is uncommittable.
102    IF (XACT_STATE()) = -1
103        BEGIN
104            PRINT 'The transaction encountered an error. Rolling back.';
105            ROLLBACK TRANSACTION;
106        END;
107
108    -- Test whether the transaction is active and valid.
109    IF (XACT_STATE()) = 1
110        BEGIN
111            COMMIT TRANSACTION;
112        END;
113END CATCH;
114CONTINUE;
115END;