merge agent error

Hi

We are using HTTPS merge replication.

One of my subscribers is getting this error:

Error messages:

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)

This is a bit surprising - it has been working fine - and also there were no changes at the publisher (it only has one article in this publication - a stored proc)

Why would this have happened ? The retention period is 45 days and they synchronised successfully only a few days prior.

thanks

1. How often (frequently) does your app do merge between the publisher and this subscriber? If you had other subscribers and have done merge sync, did the same issue raise?

2. What if you select last_sync_date and metadatacleanuptime from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights

|||

Thanks for replying.

1. It syncs up every day

2. last_sync_date = 2006-04-28 21:28:58.803
metadatacleanuptime = 2006-05-02 17:37:25.420

We have a number of clients for whom we setup merge replication. For each database we create two publications

1) the first publication has all the data and procs - it is setup with the syncType set to None. Therefore when we initialise the subscription the subscriber already has the data and schema.

2) the second publication only has one proc initially - it is setup with the syncType set to Automatic - we use this publication if we need to add any tables or procs to the database - it obviously has a much smaller snapshot

We have noticed that it is the second publication which is often getting this error - even though the clients are regularly synchronising....

Regards
Bruce

|||

Hi

Any ideas on this ?

Thanks
Bruce

|||

Hi Bruce, it looks like an existing bug in SQL 2005 which is slated to be fixed in SP2. Can you confirm that "select use_partition_groups from sysmergepublications" has value of 0 for both your publications?

Also, how many articles are in the other publication?

|||

Thanks for the response - I'm away for the next 5 days so can't get the answer on use_partition_groups yet...

In the other publication - there are about 600 I s'pose - 100 old tables and 500 procs or more.

Is there any way to stop this happening until sp2 ? We are rolling this out at the moment...

Thanks

|||One last question - you mentioned the subscribers sync daily, yet there's four days difference between last sync time and the metadata cleanup time. Do you know what happened between 4/28 and 5/2? Did the agent not sync?|||

Hi

It syncs every day if the pc is on ! This client turns it off on the weekend.

Yes - use_partition_groups is set to 0 for both publications

In case it matters the subscriptions are setup as follows:

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

As there is only one proc in the second publication at the moment (and it is a trivial proc) I can reinitialise it and I guess this will be a workaround for now - but as soon as I need to add tables to this for future releases I will not want to be doing this. Is it a matter of hanging out until SP2 for a fix for this ? I guess that will be 3/4 months away or worse.

One more question about reinitialising subscriptions. When marking the publication for reinitialization from the publisher, I right-click on the publication name and have to select 'reinitialize all subscriptions' (I'm guessing that because the subscriber type is anonymous I can't select the individual subscriber if there are multiple subscribers to this publication), I get a message box asking me to confirm and whether to use the current snapshot or to create a new one. What is most disconcerting is that it doesn't specify which publication it is doing this for. Just for my own sanity, can you confirm it is only doing this for the publication you are selecting, and not all of them !!!! Paranoid I know but it's always better to ask I think..

Thanks
Bruce

|||

hi Greg

Any further update on a fix for this ?

Thanks
Bruce

|||

Bruce,

You can wait for Service pack 2 for the fix.

As a workaround, what you can do is increase the retention period of the publications. That way, the possibility of hitting the bug is lesser.

As per your question of reintializing the publication, all subscriptions to the publication you right clicked (and chose reinitialize) will be renitialized. No other publication will be affected.

|||

FYI,

We are having the same issue and are eagerly awaiting SP2 or a hotfix.

|||

We are having a similar issue but it is the other way round.

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).

This has started to happen only after we have set-up a second publication. We have set the retention period to 5 days on both the publications. We replicate data every 3 minutes and hence 5 days is a long time for data not to replicate.

Any ideas as to why we are getting this problem? Is this a known issue?

|||

I posted a visual guide to addressing this issue at: http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2006/08/13/128.aspx.

:{> Andy

|||We have also run into this error, and have used the work around of extending our expiration out to 999 days.

I am wondering if we will be running into performance issues soon because the metadata will not be cleaned up often enough. Will the tables get extrememly large? Is there a way to do the cleanup manually if performance suffers enough?

Is SP2 close to being released?

thanks,
jg

|||

Do subscribers and publishers both need to be upgraded to SP2a for this to be fixed

We upgraded the publisher, and this is still happening.

I'll see if it happens to any of the subscribers after we upgrade them to sp2a.

Bruce