SQL Server Peer-to-Peer Replication – When a Conflict is Detected

1. On the subscription, under the all subscription tab, right click & choose “view details”.  Click on the Undistributed Commands tab.
   1. If the estimated time is less than a few minutes, and the number of commands is under a thousand or two, you’re OK.
      1. Sample of up to date(ish)
      2. 
2. Run SQL to check for Transactions
   1. Part 1: Enter you <DATABASENAME> & <PUBLICATIONNAME>

      Declare @PublisherDB sysname, @Publication sysname, @agentid sysname;   -- Set Publisher database name Set @PublisherDB = N'<DATABASENAME>'; -- Set Publication Name Set @Publication = N'<PUBLICATIONNAME>'; -- Get agent id Set @agentid = (select id from MSdistribution_agents where publisher_db =@PublisherDB and subscriber_db = @PublisherDB and Publication = @Publication) select @agentid --Get xact_seqno & delivery_rate select top 1 xact_seqno, delivery_rate from dbo.MSdistribution_history where agent_id = @agentid and runstatus in (2,3,4) order by time desc;

       

      1. Sample Output:

         ---Returned:
         xact_seqno    delivery_rate
         0x0003B66300019C4F0001000000000000    16.2109375

   2. Part 2:
      1. Note: replace <DELIVERY_RATE> and <XACT_SEQNO> with numbers from above, and enter you <DATABASENAME> & <PUBLICATIONNAME>

         -- PART 2: Declare @PublisherDB sysname, @Publication sysname, @agentid sysname;   -- Set Publisher database name Set @PublisherDB = N'<DATABASENAME>'; -- Set Publication Name Set @Publication = N'<PUBLICATIONNAME>'; -- Get agent id Set @agentid = (select id from MSdistribution_agents where publisher_db =@PublisherDB and subscriber_db = @PublisherDB and Publication = @Publication) DECLARE @avg_rate int DECLARE @retcode int DECLARE @mstestcountab TABLE ( pendingcmdcount int ) select @avg_rate = isnull(avg(<DELIVERY_RATE>),0.0)         from dbo.MSdistribution_history         where agent_id =  @agentid       insert into @mstestcountab (pendingcmdcount)         exec @retcode = sys.sp_MSget_repl_commands                                     @agent_id = @agentid                                     ,@last_xact_seqno = <XACT_SEQNO>                                     ,@get_count = 2                                     ,@compatibility_level = 9000000   select pendingcmdcount from @mstestcountab       select         pendingcmdcount         ,N'estimatedprocesstime' = case when (@avg_rate != 0.0)                                 then cast((cast(pendingcmdcount as float) / @avg_rate) as int)                                 else pendingcmdcount end     from @mstestcountab

1. Under the Publication –> Agents Tab, right click & choose Stop Agent.
   1. Refresh to check that it has stopped, then right click & choose Start Agent.
2. Under the Publication –> All Subscriptions tab, right click & choose Stop Synchronizing
   1. Refresh to check that it has stopped, then right click & choose Start Synchronizing
3. Repeat 1 & 2 for the other node in the replication cluster
4. Recheck the Undistributed Commands tab for each cluster.  It make take a minute or 2 for it to catchup & clear out the old transactions.