Solution options 

-- Drop existing temp table if it exists 

IF OBJECT_ID('tempdb..#ToDelete') IS NOT NULL 

    DROP TABLE #ToDelete; 

 

-- Select records for deletion 

SELECT ch.ComplianceHistoryID 

INTO #ToDelete 

FROM dbo.ComplianceHistory 

WHERE <Insert condition here, e.g., ch.HistoryDate < DATEADD(YEAR, -3, GETDATE())>; 

 

-- Create index for performance 

CREATE CLUSTERED INDEX IX_#ToDelete ON #ToDelete(ComplianceHistoryID); 

 

-- Batch deletion loop 

DECLARE @DeletedCount INT; 

DECLARE @BatchCount INT = 1; 

 

WHILE 1 = 1 

BEGIN 

    DELETE TOP (10000) ch 

    FROM dbo.ComplianceHistory ch 

    JOIN #ToDelete d ON d.ComplianceHistoryID = ch.ComplianceHistoryID; 

 

    SET @DeletedCount = @@ROWCOUNT; 

 

    PRINT CONVERT(NVARCHAR, GETDATE()) +  

          ': Batch ' + CONVERT(NVARCHAR, @BatchCount) +  

          ' - Deleted ' + CONVERT(NVARCHAR, @DeletedCount) + ' rows'; 

 

    IF @DeletedCount = 0 BREAK; 

    SET @BatchCount += 1; 

 

    WAITFOR DELAY '00:00:01'; -- Reduce DB load 

END 

TIP:
• Use SQL Server Agent or another scheduling mechanism to automate this script.
• You can configure a script such as this to run on a regular basis (like weekly over the weekend, or monthly). Run during low-activity periods.
• Shrink the database afterward to reclaim disk space.

Option 3: High-volume truncate and reimport  

For databases with billions of rows, a more efficient process may be required: 

1. Identify the ComplianceHistoryID for the cutoff date. 

2. Export records to keep (e.g., recent changes, licensing info). 

3. Truncate the ComplianceHistory table. 

4. Re-import only the necessary data. 

5. Drop temp/export tables. 

This method ensures a clean, fast reset of the table with preserved essential data. It’s especially helpful when the proportion of data to be kept is small.