Error The row was not found at the Subscriber when applying the replicated command for transactional replication

Question

I have a problem with SQL 2008 replication, I currently have referential integrity enforced with the use of primary and foreign keys between 2 tables, what I am finding now is that if I delete or update table A, the updates are not being shown on the subscriber database, instead I’m getting the error.

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000017FE00000091000100000000, Command ID: 1)

Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Is there anything within replication that stops such commands ?

Answer 1

Use the continue on data consistency error profile. This will skip these errors, but your databases will be out of sync. you will need to do a validation to see how out of sync your databases are and to determine which tables are out of sync.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Thanks for the reply, is this an expected behaviour or a bug ?

Also, when you say data validation do you mean just counting the number of records or is there a better way to do this ?

Answer 3

Fred, I can't comment on whether the behavior you are seeing is a bug unless you can show the schema, pub script and a typical DML statement which causes the problem.

I suspect its probably something outside of replication.

When I talk about validation this is what I am referring to:

http://msdn.microsoft.com/en-us/library/aa178831%28SQL.80%29.aspx

You can of course do this manually by counting records or doing a checksum or using a tool like RedGate's data compare.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

I found this very useful. Thanks. I ended up reinitialising the publication. I think its something to do with the fact that the table has a foreign key constraint attached to it.

Answer 5

Also, when you have indexes created on a publisher for OLTP type queries, but the articles on the subscriber are used for reporting purposes, which require a different kind of index requirements. what are your recomendations here ? This is a case where my reporting requirements needs different types of indexes from my OLTP database.

Answer 6

I apply them through a post snapshot script.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 7

Thats a fair point, but what about the indexes that will be replicated from the publisher, does this mean that it will need to be dropped first before applying the post snapshot script ?

Also, if I want to disable replication to a subscriber, i.e want to ignore/block replication to a subscriber until further notice or until I want it back, is there a stored procedure that can do this without having to drop the subscription ?

Thanks.

Answer 8

Yes, a post snapshot script will apply them again on the subscriber each time you reinitialize.

If you select the action name if in use option of "Drop existing object and create a new one" and you choose to copy non-clustered indexes each time you reinitialize the user indexes will be replicated to the subscriber and the post snapshot script will place the reporting indexes in place.

If you select any of the other options, running the post snapshot script will fail as the post snapshot script indexes will already be in place, so it is wise to do an existence check before you apply the post snapshot script indexes. Note again that any indexes on your subscriber tables will slow down the snapshot application process.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941