Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Delete statements not replicating - how to troubleshoot?

Answered Delete statements not replicating - how to troubleshoot?

  • Thursday, December 06, 2012 10:48 AM
     
     
    Sign In to Vote
    0

    I'm using SQL Server 2005 merge replication and I've noticed that when I ran a delete statement on my publisher, some rows on my subscriber were not deleted. There were approximately 180,000 rows deleted from two tables and there are 6 rows left in one table and 18 in the other that should have been deleted as a result of replication but they did not. 

    How can I troubleshoot what went wrong here?

     

All Replies

  • Thursday, December 06, 2012 3:21 PM
    Moderator
     
     
    Sign In to Vote
    0
    Can you check to see if these rows are logged as conflicts?

    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

     
  • Thursday, December 06, 2012 4:54 PM
     
     
    Sign In to Vote
    0

    Hi Hilary,

    There are no rows in the conflicts table on either the Publisher or the Subscriber. The replication monitor is not showing any conflicts either.

     
  • Friday, December 07, 2012 7:10 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi John,

    If there is any conflict, the data should be synchronized, I suggest connecting the Subscriber to the network to synchronize with Publisher again. Additionally, I want to confirm whether the issue only occurs with one subscriber or all subscribers.

    TechNet Subscriber Support
    If you are     TechNet Subscriptionuser and have any feedback on our support quality, please send your feedbackhere.

    Allen Li
    TechNet Community Support


     
  • Friday, December 07, 2012 9:04 AM
     
     
    Sign In to Vote
    0

    Hi Allen,

    I only have one subscriber at the moment as I am just testing. The subscriber was already connected to the network when I ran the delete statement on the publisher (in fact it is always connected to the network at the moment). 

    As I understand, when a row is deleted from either side, and entry is added to the MSmerge_tombstone table. I can see an entry in this table for each of the rows that should have been deleted on the Publisher but not on the Subscriber. Is it possible that it could be a network issue?

     
  • Thursday, December 13, 2012 10:12 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Hi, I want to inform that MSmerge_tombstone table contains information on deleted rows and allows deletes to be propagated to other Subscribers, if you want to get the deleted rows of the publisher, please execute the following codes:

    SELECT *
  FROM [PublisherDBName].[dbo].[MSmerge_tombstone]

    To get the the deleted rows of the subscriber, please execute the following command:

    SELECT *
  FROM [SubscriberDBName].[dbo].[MSmerge_tombstone]
    Additionally, I want to inform that the synchronization needs time to finish, we can manually synchronize the data with the following steps:
    please locate “Local Subscriptions” in SQL Server Management Studio, find the related subscription, right click it and choose “view synchronization status”, then please click “Start” to synchronize the data.

    Allen Li
    TechNet Community Support

     
  • Thursday, December 13, 2012 2:12 PM
     
     
    Sign In to Vote
    0
    Check the "Statement Delivery" section of the Article Properties of the Publisher, there's a field called "DELETE delivery format", check that it is set to "DELETE statement", I suspect it may be set as default not to replicate DELETE transactions.