Delete/ Update a large amount of records

process_large_amount_records.sql

-- got from https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
DECLARE @rows INT, @ErrorMessage NVARCHAR(4000);

SET NOCOUNT, XACT_ABORT ON;
SET @rows = 1;

BEGIN TRY
	WHILE (@rows > 0)
	BEGIN
		BEGIN TRANSACTION;
			DELETE TOP (100000) dbo.<table_name> WHERE <clause_statement>;
			SET @rows = @@ROWCOUNT;
		COMMIT TRANSACTION;
	END;
END TRY
BEGIN CATCH
	PRINT N'Error while purging <table_name>';
	SELECT @ErrorMessage = ERROR_MESSAGE();
	PRINT @ErrorMessage; 
	IF (XACT_STATE()) = -1 
		ROLLBACK TRANSACTION;
	IF (XACT_STATE()) = 1 
	 	COMMIT TRANSACTION;
	THROW;
END CATCH;