locked
Merge replication metadata deletion error MSSQL_REPL-2147199402 RRS feed

  • Question

  • I setup merge replication about 3 weeks ago between a few databases all running SQL 2008 SP1:

    • DB1 - This database is the publisher and distributor.
    • DB2 - This database is a write-only subscriber (-EXCHANGETYPE 1) to DB1.
    • DB3 - This database is a write-only subscriber (-EXCHANGETYPE 1) to DB1.
    • DB4 - This database is a write-only subscriber (-EXCHANGETYPE 1) to DB1.
    • DB5 - This database is a read-only subscriber (-EXCHANGETYPE 2) to DB1.
    • DB6 - This database is a read-only subscriber (-EXCHANGETYPE 2) to DB1.

    Everything was running fine, then suddenly one at a time the replication jobs for DB2, DB3, and DB4 all started reporting the error:

    The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)

    I was able to manually upload data from DB2, DB3, and DB4 to DB1 then reinitialize the subscriptions.  However, I don't understand why only those three subscriptions failed.  I have other merge replication jobs between the same SQL instances that are still running fine.  The only unique thing I can determine about the failed subscriptions is that they are write-only.  I really don't want to make them read/write, as it would cause a lot of unnecessary network traffic to flow between them.

    Based on what I've been able to gather, the subscriptions were marked as expired which caused this problem.  If I change subscriptions to never expire to get around this problem, is that going to cause my distribution DB to grow unchecked?  Are there other ways to solve this problem?

    Thanks,

    Matt

    Tuesday, April 12, 2011 3:00 PM

Answers

All replies

  • Changing your subscriptions to never expire will merely keep existing replication metadata around forever. It might solve the problem for you, but then it will results in very large database.

    This error is likely due to an oustanding bug with the cleanup process.

    The distribution database will not get large, but your publisher and subscriber databases will.

    There is no way I know of getting around this problem - you might want to try to postpone the metadata clean up with the -MetadataRetentionCleanup switch to 0.


    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
    • Proposed as answer by Peja Tao Thursday, April 14, 2011 6:46 AM
    • Marked as answer by Alex Feng (SQL) Thursday, April 21, 2011 3:18 AM
    Tuesday, April 12, 2011 3:24 PM
    Answerer
  • I think postponing metadata cleanup would prevent the problem from happening at certain times, but I'm just postponing the inevitable pain.

    What if I reinitialized subscriptions on a regular basis?  There is some down time each week when I know no other activity will be happening in the affected tables.  If re-initializing fixes the problem, could it prevent the problem if I keep doing it over and over again before the subscriptions would otherwise expire?


    Tuesday, April 12, 2011 3:33 PM
  • That would be an idea. I would use sp_removedbreplication on the database after dropping the subscription to ensure everything is cleaned up.

    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
    • Proposed as answer by Peja Tao Thursday, April 14, 2011 6:46 AM
    • Marked as answer by Alex Feng (SQL) Thursday, April 21, 2011 3:18 AM
    Tuesday, April 12, 2011 4:06 PM
    Answerer
  • I read a fix for similar error MSSQL_REPL_2147199401

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/67cc93cf-f59d-4ca9-bf5e-aaad72c63864/

    Solution given was
    - backup db at publisher and subscribers;
    - at subscribers: update sysmergepublications set cleanedup_unsent_changes = 0 where cleanedup_unsent_changes = 1 and subscriber_server = 'Publisher' (replacing publisher by your publisher of course);
    - start replication (this should work now);
    - validate all subscriptions

    I followed these steps but it didnt fix my problem. error which was shown to me was MSSQL_REPL_2147199402.

    The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription(without upload).


    i didnt want to re-initialize the subscription as lot of data transfer  and data loss because of reinitializing without upload would have been resulted.

    Then i updated the cleanedup_unsent_changes=0 on Publisher in sysmergepublication for the Subscription which was throwing error and started the replication for subscriber and it worked for me :)

    Below was what i ran on Publisher (also i had run same script on subscriber too previously, so not sure if running this script on subsciber is necessary)

     update sysmergepublications set cleanedup_unsent_changes = 0 where cleanedup_unsent_changes = 1 and subscriber_server = 'Subscriber'
    


    • Proposed as answer by Aamod Saturday, November 19, 2011 8:54 PM
    Saturday, November 19, 2011 8:53 PM
  • I read a fix for similar error MSSQL_REPL_2147199401

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/67cc93cf-f59d-4ca9-bf5e-aaad72c63864/

    Solution given was
    - backup db at publisher and subscribers;
    - at subscribers: update sysmergepublications set cleanedup_unsent_changes = 0 where cleanedup_unsent_changes = 1 and subscriber_server = 'Publisher' (replacing publisher by your publisher of course);
    - start replication (this should work now);
    - validate all subscriptions

    I followed these steps but it didnt fix my problem. error which was shown to me was MSSQL_REPL_2147199402.

    The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription(without upload).


    i didnt want to re-initialize the subscription as lot of data transfer  and data loss because of reinitializing without upload would have been resulted.

    Then i updated the cleanedup_unsent_changes=0 on Publisher in sysmergepublication for the Subscription which was throwing error and started the replication for subscriber and it worked for me :)

    Below was what i ran on Publisher (also i had run same script on subscriber too previously, so not sure if running this script on subsciber is necessary)

     update sysmergepublications set cleanedup_unsent_changes = 0 where cleanedup_unsent_changes = 1 and subscriber_server = 'Subscriber'
    


    Sorry to dredge up this old thread. We're in this exact scenario. After you ran these queries did the issue resurface? Did you do anything after this to check data integrity of the DB?

    Thanks

    Tuesday, November 11, 2014 3:02 AM
  • Hi all

    We have today this problem too. this is the four time it happens y 6 months. Can someone please tell me if this fix this problem in SQL Server 2012 SP1

    Thanks for your help.

    James.

    Monday, October 5, 2015 10:16 PM
  • I have not seen it yet, but that does not necessarily mean it is solved.


    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, October 5, 2015 11:14 PM
    Answerer
  • Thanks for your answer,

    In general, there is no fix to this problem except to reinitialize the suscriptor ? What says Microsoft about it. My client is starting to worry beacause it has to be done in the night.

    All four servers sql 2012 SP1.

    Thank you for your answer.

    James

    Thursday, October 8, 2015 6:05 PM
  • Your best solution here is to open up a support incident with Microsoft.


    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, October 12, 2015 2:48 PM
    Answerer
  • Thanks Hilary,

    I will have to do that.

    James

    Monday, October 12, 2015 5:12 PM