Need to delete Millions of records from table which is part of Transaction Replication

Question

Need to delete Millions of records from table which is part of Transaction Replication

Sowjanya Gopal 120

I have transaction replication on a table. I have to delete millions of records from it. I want to create a SP to delete rows & replicate execution of SP. A few records mismatch might be there between Publisher & Subscriber. Will it cause issue.

And I am confused if I have to Replicate Execution of Stored Proc / Execution in a Serialized Transaction of the SP.

Deepesh Dhake 1,245 Reputation points

2026-09-22T03:53:57.7066667+00:00

is your delete criteria-based (archiving old rows) or a fixed list of PKs? Criteria-based logic is where the determinism risk lives, so if it's a fixed PK list, the picture is a bit safer.
Sowjanya Gopal 120 Reputation points

2026-09-22T17:06:51.6+00:00

I want the deletion to be criteria-based, either using a range of the DateKey column (for example, 20260922) or a range of primary key values.

My main concern is to avoid any conflicts and ensure that transaction replication does not break.

I have already taken a full backup of the data on both the Publisher and Subscriber servers.

I am okay with minor data mismatches between Publisher and Subscriber during the deletion:

If a record does not exist on the Publisher but exists on the Subscriber, it is fine if that record gets deleted from the Subscriber.

Similarly, if a record exists on the Publisher but does not exist on the Subscriber, it is fine if the Publisher-side record gets deleted.

Given these conditions, can I use Stored Procedure execution or Execution in a serialized transaction of the SP approach for the deletion so that the procedure execution itself is replicated, rather than replicating millions of individual DELETE statements?

The main objective is to delete the required data efficiently while ensuring that replication remains healthy without any latency and does not break.

Answer accepted by question author

Deepesh Dhake 1,245

Use plain proc exec (not serializable). Serializable silently reverts to millions of row-by-row DML commands if any call runs outside a serializable transaction - the exact volume you're avoiding. You've waived consistency, so plain proc exec is simpler with no such failure mode.

Why it won't break replication: your EXEC bypasses the default per-row delete procs that raise "row not found" (20598). A Publisher-only in-range row just matches nothing on the Subscriber no error, no stall.

Pattern bounded ranges, looped EXEC (the loop isn't replicated, each EXEC is):

CREATE PROCEDURE dbo.usp_DeleteByRange @RangeStart BIGINT, @RangeEnd BIGINT
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM dbo.YourTable
    WHERE YourKeyColumn BETWEEN @RangeStart AND @RangeEnd;  -- DateKey or PK
END;

DECLARE @lo BIGINT = 1, @hi BIGINT = 50000000, @batch BIGINT = 20000;
WHILE @lo <= @hi
BEGIN
    EXEC dbo.usp_DeleteByRange @lo, @lo + @batch - 1;
    WAITFOR DELAY '00:00:01';
    SET @lo += @batch;
END;

Rules:

Pass bounds as literal parameters so no GETDATE() inside the proc (it re-evaluates on the Subscriber).

Index the predicate on both sides - the Subscriber re-runs the same delete; an unindexed scan there is your main latency risk.

Keep the proc minimal and batches small (lock/log control).

DateKey (YYYYMMDD) isn't contiguous - step by PK range, or by actual distinct DateKeys.

Sowjanya Gopal 120 Reputation points

2026-09-29T07:18:20.9033333+00:00

Thank you so much for your inputs. Will implement this approach.

3 additional answers

Your answer

Deepesh Dhake 1,245 Reputation points

2026-09-22T03:53:57.7066667+00:00

is your delete criteria-based (archiving old rows) or a fixed list of PKs? Criteria-based logic is where the determinism risk lives, so if it's a fixed PK list, the picture is a bit safer.
Sowjanya Gopal 120 Reputation points

2026-09-22T17:06:51.6+00:00

I want the deletion to be criteria-based, either using a range of the DateKey column (for example, 20260922) or a range of primary key values.

My main concern is to avoid any conflicts and ensure that transaction replication does not break.

I have already taken a full backup of the data on both the Publisher and Subscriber servers.

I am okay with minor data mismatches between Publisher and Subscriber during the deletion:

If a record does not exist on the Publisher but exists on the Subscriber, it is fine if that record gets deleted from the Subscriber.

Similarly, if a record exists on the Publisher but does not exist on the Subscriber, it is fine if the Publisher-side record gets deleted.

Given these conditions, can I use Stored Procedure execution or Execution in a serialized transaction of the SP approach for the deletion so that the procedure execution itself is replicated, rather than replicating millions of individual DELETE statements?

The main objective is to delete the required data efficiently while ensuring that replication remains healthy without any latency and does not break.
Sowjanya Gopal 120 Reputation points

2026-09-29T07:18:20.9033333+00:00

Thank you so much for your inputs. Will implement this approach.

Answer 1

Could you imagine to go with normal DELETE in batches?

For example delete 50k rows, commit, then continue with next batch. This should be much better for transaction log and replication than one very big delete.

I would be careful with stored procedure replication here. If publisher and subscriber already aren't 100% the same, same DELETE condition can remove different rows on subscriber.

So better first try batched delete on publisher and let replication handle the row deletes. check the replication latency and log growth.

If this is still too slow (because of very huge number of rows), then stored procedure execution can be considered, but only if both sides are really in sync.

Answer 2

Harsh meena 0

If you execute a normal DELETE statement on the Publisher, transactional replication reads the logged row changes from the transaction log and sends those DELETE operations to the Subscriber. In that case, existing data mismatches are usually less of a concern because the replicated commands target specific rows identified by the article's key columns.

However, if you configure stored procedure execution replication, the procedure itself is executed on the Subscriber. If the Publisher and Subscriber are not perfectly synchronized, a criteria-based delete such as:

0 comments

Answer 3

Bruce (SqlWork.com) 85,456

transaction replication uses the transaction log (recorded page changes). all logged transaction appear in the log. as long as the sp does not perform unlogged transactions (bulk insert or truncate table), then it will replicate properly.

Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2026-09-21T20:52:23.8+00:00

as long as the sp does not perform unlogged transactions (bulk insert or truncate table), then it will replicate properly.

There is no such thing as unlogged transactions. If you are in simple or bulk_logged recovery, some transactions may be minimally logged. I don't know for sure what happens with these, but I would be surprised if they did not replicate. Then again, they may not be minimally logged if replication is enabled. TRUNCATE TABLE, by the way, is fully logged.

As for the actual question, I agree with Bruce: just do it. With one qualification: If you are going to delete tens of millions of rows, it may be better to split up the deletion in batches of, say, 100 000 rows at a time. (The exact batch size depends on how big your rows are. If the table is wide or there are big LOB columns, maybe you should hold down the batch size even more. On the other hand, if the table is narrow, likely only five int column, you can go for a million rows at a time.) You should commit after each batch, as the point is to hold down transaction log in size.