locked
Intermittenly replication doesn't bring across the insert statement to be executed on the subscriber RRS feed

  • Question

  • We are using SQL 2014 enterprise and transactional push publication. Distributor is on its own SQL server. The subscriber is on SQl 2012 enterprise.   We are a 4 node cluster using Always-ON.   Our replication process has been error-free for over two years until couple days ago.   I had two instances so far that the error alert indicates the problem was due to "The
    row was not found at the subscriber when applying the replicated command".   Replication was trying to update a record but that record no longer exists.

    To trouble shoot, I checked the permission on all users on that db and they are all read only.  I checked the profiler trace (server side trace) and searched for any delete statement issued by any users or by replication and found none.   I checked the inserting sequence which is based on an identity key, when a record was being inserted in the publisher db, say in a half our span, it inserted  record # 1001, 1002, 1003, 1004, the profiler trace on the subscriber showed the insert for #1001, 1002, 1004.  For reason,  SQL replication didn't replicate the change command for record #1003. 

    In our replication setup, we don't use filter or any specialized custom settings.   Basically we use the default setup to keep thing simple to manage.   This is the very first time we see this type of 'skip insert' situation.    This past week our publisher server has been very busy and I did see some significant latency during the day time and night time was back to normal.   Strangely enough that this 'skip insert' problem happened in the evening.  One happened at about 8 pm CST and one happened at 11:40 pm.  Does anyone experience this type of problem or know how to track down the root cause? 

    Thanks in advance,

    OD


    Ocean Deep

    Friday, December 4, 2015 5:07 PM

Answers

  • Take look at below link

    https://www.mssqltips.com/sqlservertip/3351/auto-fix-sql-server-transactional-replication-error-20598-for-updates/

    I also faced this kind of issue some time back, I just follow the below and it auto fixed after some time. Even I removed the skiperror code from the agent after a day.

    https://www.mssqltips.com/sqlservertip/2469/handling-data-consistency-errors-in-sql-server-transactional-replication/

    Thursday, December 10, 2015 9:32 AM

All replies

  • Hi OD,

    Firstly, please follow the steps in this KB article to troubleshoot this issue.

    Secondly, to work around this issue,  you can use the following two methods:

       • Manually insert the missing row at the subscriber. This may enable the Distribution Agent to retry the failed command and move forward with the replication.

       • Instruct the Distribution Agent to skip this error and continue to replicate the rest of the changes.

    Thirdly,  to investigate the cause of this issue, please collect replication agent logs and other information as mentioned in the KB article.

    There is also a similar blog for your reference.
    http://www.pythian.com/blog/sql-server-replication-quick-tips/ 

    Thanks,
    Lydia Zhang

    If you have any feedback on our support, please click here.


    Lydia Zhang
    TechNet Community Support



    Monday, December 7, 2015 6:52 AM
  • Have there been any schema changes on the problem table recently?

    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

    Monday, December 7, 2015 6:52 PM
    Answerer
  • This kind of problem can be caused by unique indexes and foreign keys on the subscriber.  Do those exist on the table in question?

    Monday, December 7, 2015 7:08 PM
  • Lydia,

    Thanks for the reply.  I did read about that article prior.  I did some of the suggested steps but one step I can't do due to business reason is to turn on the profiler trace on the publisher db. 

    As I mentioned in my original post, I turned on the profiler trace (server side trace) on the subscriber and didn't find the insert statement executed on subscriber.   Is there a way for me to find or confirm that the same insert statement that happened on the publisher did or didn't get pass to the distributor?  Is there a query statement I can use to look for that statement?

    OD


    Ocean Deep

    Wednesday, December 9, 2015 3:38 PM
  • Take look at below link

    https://www.mssqltips.com/sqlservertip/3351/auto-fix-sql-server-transactional-replication-error-20598-for-updates/

    I also faced this kind of issue some time back, I just follow the below and it auto fixed after some time. Even I removed the skiperror code from the agent after a day.

    https://www.mssqltips.com/sqlservertip/2469/handling-data-consistency-errors-in-sql-server-transactional-replication/

    Thursday, December 10, 2015 9:32 AM