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?
Best Answer
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