Delete millions of rows from a SQL table https://dba.stackexchange.com/questions/189607

189607_v1.sql

PRINT 'Drop temporary table if exist';
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
    BEGIN
        DROP TABLE #MyTempTable;
    END;
GO

CREATE TABLE #MyTempTable
    (
        Id BIGINT,
        BatchNumber BIGINT,
        PRIMARY KEY(BatchNumber, Id)
    );
GO

INSERT INTO #MyTempTable
SELECT nv.VendorId,
       1 + (ROW_NUMBER() OVER (ORDER BY nv.VendorId) / 4500) AS BatchNumber
FROM   MySourceTable nv
WHERE  NOT EXISTS (
                      SELECT *
                      FROM   dbo.vendor AS v
                      WHERE  nv.VendorId = v.Id
                  );
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @BATCHSIZE INT,
        @ITERATION INT,
        @BATCHNUMBER INT,
        @TOTALROWS INT,
        @MSG VARCHAR(500);
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;

BEGIN TRY
    BEGIN TRANSACTION;

    WHILE @BATCHSIZE > 0
        BEGIN
            SET @BATCHNUMBER = @ITERATION + 1;

            DELETE FROM MySourceTable
            OUTPUT DELETED.*
            INTO MyBackupTable
            WHERE VendorId IN (
                                  SELECT nvwv.Id
                                  FROM   #MyTempTable AS nvwv
                                  WHERE  BatchNumber = @BATCHNUMBER
                              );

            SET @BATCHSIZE = @@ROWCOUNT;
            SET @ITERATION = @ITERATION + 1;
            SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
            SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);
            PRINT @MSG;
            COMMIT TRANSACTION;
            CHECKPOINT;
        END;
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
       AND @@TRANCOUNT > 0
        BEGIN
            PRINT 'There is an error occured.  The database update failed.';
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO