locked
Merge Replication stops - how do I diagnose the specific error(s) causing it to stop? RRS feed

  • Question

  • Hello SQL Server community,

    I have a situation where merge replication stops immediately when an application inserts a record into two tables.

    The publisher is running SQL Server 2008. The subscriber is a new installation running SQL Server 2008 R2.

    Below is a snip of the log when the failure occurred with the errors highlighted in bold. These are rather generic error messages.

    Question is: How does one get details on why an insert or update to a specific table ('Reservations' in this case) caused replication to stop?

     

    Thanks in advance!

     

    2011-07-28 00:15:08.998 Connecting to OLE DB Subscriber at datasource: 'SRVR-DC\DCIMPROV', location: '', catalog: 'DC', providerstring: '' using provider 'SQLNCLI10'
    2011-07-28 00:15:09.586 OLE DB Subscriber: SRVR-DC\DCIMPROV
                DBMS: Microsoft SQL Server
                Version: 10.50.1617
                catalog name: DC
                user name: dbo
                API conformance: 0
                SQL conformance: 0
                transaction capable: 1
                read only: F
                identifier quote char: "
                non_nullable_columns: 0
                owner usage: 15
                max table name len: 128
                max column name len: 128
                need long data len:
                max columns in table: 1000
                max columns in index: 16
                max char literal len: 131072
                max statement len: 131072
                max row size: 131072
    2011-07-28 00:15:10.056 Connecting to OLE DB Publisher at datasource: 'SQL2008CLUSTER', location: '', catalog: 'DC', providerstring: '' using provider 'SQLNCLI10'
    2011-07-28 00:15:10.068 OLE DB Publisher: SQL2008CLUSTER
                DBMS: Microsoft SQL Server
                Version: 10.00.2531
                catalog name: DC
                user name: dbo
                API conformance: 0
                SQL conformance: 0
                transaction capable: 1
                read only: F
                identifier quote char: "
                non_nullable_columns: 0
                owner usage: 15
                max table name len: 128
                max column name len: 128
                need long data len:
                max columns in table: 1000
                max columns in index: 16
                max char literal len: 131072
                max statement len: 131072
                max row size: 131072
    2011-07-28 00:15:10.075 OLE DB Subscriber 'SRVR-DC\DCIMPROV': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:10.294 Percent Complete: 0
    2011-07-28 00:15:10.297 Enumerating deletes in all articles
    2011-07-28 00:15:10.300 OLE DB Distributor 'SQL2008CLUSTER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:10.422 OLE DB Subscriber 'SRVR-DC\DCIMPROV': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:10.577 Percent Complete: 0
    2011-07-28 00:15:10.580 Enumerating inserts and updates in article 'Reservations'
    2011-07-28 00:15:10.583 OLE DB Distributor 'SQL2008CLUSTER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:11.008 The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2011-07-28 00:15:11.015 OLE DB Subscriber 'SRVR-DC\DCIMPROV': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:11.177 Percent Complete: 0
    2011-07-28 00:15:11.180 No data needed to be merged.
    2011-07-28 00:15:11.183 OLE DB Distributor 'SQL2008CLUSTER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:11.188 OLE DB Subscriber 'SRVR-DC\DCIMPROV': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:11.379 Percent Complete: 0
    2011-07-28 00:15:11.387 The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2011-07-28 00:15:11.390 OLE DB Distributor 'SQL2008CLUSTER': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    2011-07-28 00:15:11.551 Percent Complete: 0
    2011-07-28 00:15:11.554 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147200999
    Message: The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
    2011-07-28 00:15:11.558 The Merge Agent was unable to update information about the last synchronization at the Subscriber. Ensure that the subscription exists at the Subscriber, and restart the Merge Agent.
    2011-07-28 00:15:11.563 The merge process was unable to update last synchronization information at the Publisher.
    2011-07-28 00:15:11.567 Disconnecting from OLE DB Subscriber ...

    Saturday, July 30, 2011 9:33 PM

Answers

  • An update,

    Guys, thanks for the help offered! We did a failover this morning - essentially switching from one server to another and restarting SQL. This seems to have "healed" whatever was breaking it. Could have been the aforementioned generation leveling issue. In any case replication seems to be running so far.

    We'll be watching things today and see if this continues to work...

     

    - Oren

    • Proposed as answer by Peja Tao Tuesday, August 2, 2011 5:55 AM
    • Marked as answer by Peja Tao Friday, August 5, 2011 2:35 PM
    Monday, August 1, 2011 8:45 PM

All replies

  • cont'd...

    I will add that this problem is specific to a new subscriber running SQL Server 2008 R2/Small Business Server 2011.

    We have not had this problem on other installations running SQL 2008 and earlier versions of SBS.

     

    Thanks again!

    - Oren

    Saturday, July 30, 2011 9:46 PM
  • Hi Oren,

    Out of curiosity, does this return anything?

    SELECT *
    FROM MSmerge_genhistory
    WHERE genstatus = 4
    

    Also, sometimes this issue can be resolved by setting generation_leveling_threshold to 0.  The default is 1000.  Kill the current sync process then try this on the publisher:

    UPDATE sysmergepublications
    SET generation_leveling_threshold = 0
    

    Then re-sync.  Does it now finish synchronizing?

    If so, when it finishes, set generation_leveling_threshold back to 1000.

    I'm not entirely sure why this problem occurs.  You may want to contact Microsoft Support to help you troubleshoot this one.


    Hope this helps.
    www.sqlrepl.com

    Saturday, July 30, 2011 10:44 PM
  • Thanks Brandon, I'll give that a shot tonight when I am on those machines.

    Yes, it's a weird issue - I've seen some speculation online that it may be an SQL 2008 R2 bug.

    There's no MSmerge_genhistory in my subscription db at the moment. I'll have to set up the subscription again and watch that MSmerge_genhistory table.

     

    Thanks again,

    - Oren

    Saturday, July 30, 2011 11:29 PM
  • Hi Brandon,

    SELECT *
    FROM MSmerge_genhistory
    WHERE genstatus = 4

    did return several records.

    Sunday, July 31, 2011 2:10 AM
  • Right.  Query it again, are the same records still present in the results?  I've experienced "sticky", or interrupted generations in MSmerge_genhistory before.

    You might want to try setting generation_leveling_threshold to 0 on the publisher.  This may or may not help.


    Hope this helps.
    www.sqlrepl.com

    Sunday, July 31, 2011 2:23 AM
  • Ok - I'll check for "stickies" later this eve when we start replication, and try temporarily setting the generation_leveling_threshold to 0 and see if that works.

    Thanks

    Sunday, July 31, 2011 2:41 AM
  • Please run your agent again with -OutputVerboseLevel 4

    Add this to your batch file or job step at the very end.

    Then post back the last 100 or so lines of your output here. This should reveal where it is getting stuck.


    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
    Sunday, July 31, 2011 11:30 AM
    Answerer
  • An update,

    Guys, thanks for the help offered! We did a failover this morning - essentially switching from one server to another and restarting SQL. This seems to have "healed" whatever was breaking it. Could have been the aforementioned generation leveling issue. In any case replication seems to be running so far.

    We'll be watching things today and see if this continues to work...

     

    - Oren

    • Proposed as answer by Peja Tao Tuesday, August 2, 2011 5:55 AM
    • Marked as answer by Peja Tao Friday, August 5, 2011 2:35 PM
    Monday, August 1, 2011 8:45 PM