merge client failing

Hi

We have sql2005 merge replication happening - it is using replisapi.dll over the net.

One of the clients has been working fine, until yesterday afternoon - we are running sql2005 sp1 at subscriber and distributer

The message is as follows....

Please help !

Error messages:

The process could not read the request message due to OS error 10054. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147014842)
Get help: http://help/MSSQL_REPL-2147014842

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199374)
Get help: http://help/MSSQL_REPL-2147199374

The subscription to publication 'yarraman main' could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201019)
Get help: http://help/MSSQL_REPL-2147201019

OS error 10054 is "An existing connection was forcibly closed by the remote host.", which could mean security or network issue. Could this be the case?

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

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

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

sql

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

Memory optimization

Hi
We are running full text search with 4GB RAM on SQL server 2000. Ihave the
/3G option on in boot.ini. The VM setting is set at 2048 to 4095.
I read it in the manual that the optimal configuration for full text search
is
1. The virtual memory size to at least 3 times the physical memory installed
in the computer.
2. The SQL Server max server memory server configuration option to 1.5 times
the physical memory (half the virtual memory size setting).
Questions:
1. How do I set max sql server memory to 1.5 times physical memory? Max
memory that SQL server allows us to set is 4095 MB? Is this only possible
with AWE?
2. My observation when I made the VM 3X the physical, was that the full text
queries were slower.
What are the right settings and what am I doing wrong?
Matt
Hi
Can any body throw some ideas on this one?
MM
"ISTS" wrote:

> Hi
> We are running full text search with 4GB RAM on SQL server 2000. Ihave the
> /3G option on in boot.ini. The VM setting is set at 2048 to 4095.
> I read it in the manual that the optimal configuration for full text search
> is
> 1. The virtual memory size to at least 3 times the physical memory installed
> in the computer.
> 2. The SQL Server max server memory server configuration option to 1.5 times
> the physical memory (half the virtual memory size setting).
> Questions:
> 1. How do I set max sql server memory to 1.5 times physical memory? Max
> memory that SQL server allows us to set is 4095 MB? Is this only possible
> with AWE?
> 2. My observation when I made the VM 3X the physical, was that the full text
> queries were slower.
> What are the right settings and what am I doing wrong?
> --
> Matt