Transnational replication for Delete command RRS feed

  • Question

  • I have setup MSSQL transaction replication setup in our environment.

    SQL version: SQL Server 2014

    Server1 as publisher Server2 as Subscriber1 Server3 as Subscriber2

    Both subscriber is PULL type one way (Transaction) replication.

    Query: When I deleted 100000 row at publisher then it's deletes same no of rows at subscriber1 but at subscriber2 it's deleted more than 100000 rows.

    To stop this I have to cleanup distribution and stop deletion.

    What can be the issue ?

    Friday, August 18, 2017 5:35 AM

All replies

  • Hi Dhaval2410,

    It is a strange problem and I have not seen a similar problem before. 

    Actually, it is normal that data at the publisher and subscriber do not match, this problem is always caused by Triggers, Constraint violations. We can see some operation did not do on the subscriber in this scenario. But it is not normal to see that subscriber do more operation.

    Have you checked the data in Subscriber before operating the delete statement? Next time, you can create a profiler to catch all the operation.

    Best Regards,

    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Friday, August 18, 2017 8:09 AM
  • Its hard to say what is going on here. It could be something left over from immediate sync. You will need to implement tracing and audit triggers to track it down.

    You can bypass the error by using the continue on data consistency errors profile.

    • Proposed as answer by Teige Gao Monday, September 4, 2017 5:51 AM
    Friday, August 18, 2017 10:51 PM
  • Hi Dhaval,

    Did you delete all the 100,000 rows from one table or from multiple tables ?

    1. Please check if the table/tables from which rows were deleted have any trigger/stored procedure associated with them

    2. while deleting the rows on the publication db, did you use any SP to delete the rows or it was done through the individual delete commands ?

    3. if you can, you can use a test environment to reproduce the issue. You can keep the distribution agent disabled and then run the deletes on the publication database. After that see the log reader agent running and then run the SP sp_browsereplcmds on the distribution databases on the distributor server to check how many delete rows do you see ? If the number of delete rows are same as the number of rows deleted from publication db, then there is no way Transactional replication is not working fine. In that case you will have to go to the first step mentioned above. If you see any discrepancy in the above SP output, then let me know, we can investigate further

    Gaurav Mathur | Please mark solved if I've answered your question
    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Edited by Gaurav.Mathur Thursday, August 24, 2017 1:37 PM
    • Proposed as answer by Teige Gao Monday, September 4, 2017 5:51 AM
    Thursday, August 24, 2017 1:37 PM