Bulk DELETEs in publisher breaking SQL Server Replication in subscriber

Question

We have SQL Server replication set up between 2 databases running SQL Server 2019. Every night, a SQL Agent job runs on the publisher that deletes all the data from a table and then rebuilds the table with new data. However, for some reason, this seems to cause our replication to break on the subscriber with errors such as this:

The row was not found at the Subscriber when applying the replicated DELETE command for Table '[dbo].TABLENAME' with Primary Key(s): [Id] = 240843 (Source: MSSQLServer, Error number: 20598)

As a result, I need to fix this by manually 'filling in' the missing rows, so they can be deleted by the subscriber. Is there any way to fix this, or are bulk deletes like this something that isn't supported, or best practice with SQL Replication?

Answer 1

I faced a similar issue recently. From what I have discovered is that the SQL Server Replication for some strange reason is dividing one batch delete into multiple 'EXEC sp_MSDel_TableName' procedure invocations. So for example the following DELETE statement:

DELETE FROM MyTable WHERE ID IN (1,2,3)

will be splitted into the following multiple calls:

EXEC sp_MSDel_MyTable 1;
EXEC sp_MSDel_MyTable 2;
EXEC sp_MSDel_MyTable 3;

The very big issue with such approach that may appear is that if there are Foreign keys to the same table (for example hierarchy of rows with ParentID, so rows can depend on each other), then these invocations must be performed in a very specific order. Yet the SQL Replications ignores the order and invokes them in a random one. If for example row with ID 1 is a parent for row with ID 2, then the above example immediately results in a Foreign Key violation on the subscriber and replication error 20598 is being raised as a result.

I think there is no easy fix for such situations other than change application code to make an UPDATE statement which sets foreign key columns pointing to the same table to NULL value just before invoking a batch DELETE statement.

Answer 2

It sounds like the root cause of your error is that someone/thing is deleting the rows on the subscriber prior to the replication trying to delete the same rows. I would try and address this by tracking down who/what is removing the rows and then restricting their permissions.

Doing a bingle for the error number brings up this automation with Powershell to try and "repair" these errors automatically - https://www.mssqltips.com/sqlservertip/3351/auto-fix-sql-server-transactional-replication-error-20598-for-updates/

but better to address the root cause IMO