During a memory- and CPU-intensive nightly database update process, the
merge agents (running on the server) for two subscribers failed with
SQL Server Error 8645 (A time out occurred while waiting for memory
resources to execute the query). It's not surprising to see this error,
but the fact that the agents did not retry as configured is surprising.
I discovered that this message is listed as a "severity" of 17 - is
there some threshold that prevents a retry by the agent? A restart of
the agent was successful.
Justin H.
How many merge agents do you have? Are you limiting the number of concurrent
merge agents?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Justin H." <jhenry@.gmail.com> wrote in message
news:1134585801.416828.82300@.g44g2000cwa.googlegro ups.com...
> During a memory- and CPU-intensive nightly database update process, the
> merge agents (running on the server) for two subscribers failed with
> SQL Server Error 8645 (A time out occurred while waiting for memory
> resources to execute the query). It's not surprising to see this error,
> but the fact that the agents did not retry as configured is surprising.
>
> I discovered that this message is listed as a "severity" of 17 - is
> there some threshold that prevents a retry by the agent? A restart of
> the agent was successful.
> Justin H.
>
|||There are only two subscribers, so two merge agents with no concurrency
limit configured. I'm not so concerned about the error as long as a
retry happens as configured.
Showing posts with label subscribers. Show all posts
Showing posts with label subscribers. Show all posts
Monday, March 26, 2012
Merge Agent retrying forever
We have a Merge Replication with Publisher and Distributor on SQL Server 2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing through VPN phone lines.
From time to time one or more subscribers can't reconnect to the Publisher. By inspecting their Merge Agents (through the Replication Monitor) in the Publisher Server we see that they are marked as retrying forever. In the distribution database MSmerge_hi
story table these agents have the "runstatus" column marked with 5 (retry).
We are not able to stop the agents. We have shut down the Publisher server but still the agents show the same status. We have noticed that in order to solve the problem we must synchronize those subscribers through UTP fast cables.
Any help will be appreciated
Righth click on the merge agent and click on Agent Properties. Click on the
Steps tab and double click on a step. On the Edit Step window, click on
the Advanced tab and change the number of retry attempts to a number other
than the default (usually 10 for the run Agent step and 0 for the other
two). I put 2 for all of my steps. You should also change the retry
interval, I put 1 for mine. Click apply and next to set the next step.
Hope this helps.
"Tony2000" <anonymous@.discussions.microsoft.com> wrote in message
news:F71724A8-457A-4B2F-BB8D-B92B0EC3ADD2@.microsoft.com...
> We have a Merge Replication with Publisher and Distributor on SQL Server
2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing
through VPN phone lines.
> From time to time one or more subscribers can't reconnect to the
Publisher. By inspecting their Merge Agents (through the Replication
Monitor) in the Publisher Server we see that they are marked as retrying
forever. In the distribution database MSmerge_history table these agents
have the "runstatus" column marked with 5 (retry).
> We are not able to stop the agents. We have shut down the Publisher server
but still the agents show the same status. We have noticed that in order to
solve the problem we must synchronize those subscribers through UTP fast
cables.
> Any help will be appreciated
sql
From time to time one or more subscribers can't reconnect to the Publisher. By inspecting their Merge Agents (through the Replication Monitor) in the Publisher Server we see that they are marked as retrying forever. In the distribution database MSmerge_hi
story table these agents have the "runstatus" column marked with 5 (retry).
We are not able to stop the agents. We have shut down the Publisher server but still the agents show the same status. We have noticed that in order to solve the problem we must synchronize those subscribers through UTP fast cables.
Any help will be appreciated
Righth click on the merge agent and click on Agent Properties. Click on the
Steps tab and double click on a step. On the Edit Step window, click on
the Advanced tab and change the number of retry attempts to a number other
than the default (usually 10 for the run Agent step and 0 for the other
two). I put 2 for all of my steps. You should also change the retry
interval, I put 1 for mine. Click apply and next to set the next step.
Hope this helps.
"Tony2000" <anonymous@.discussions.microsoft.com> wrote in message
news:F71724A8-457A-4B2F-BB8D-B92B0EC3ADD2@.microsoft.com...
> We have a Merge Replication with Publisher and Distributor on SQL Server
2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing
through VPN phone lines.
> From time to time one or more subscribers can't reconnect to the
Publisher. By inspecting their Merge Agents (through the Replication
Monitor) in the Publisher Server we see that they are marked as retrying
forever. In the distribution database MSmerge_history table these agents
have the "runstatus" column marked with 5 (retry).
> We are not able to stop the agents. We have shut down the Publisher server
but still the agents show the same status. We have noticed that in order to
solve the problem we must synchronize those subscribers through UTP fast
cables.
> Any help will be appreciated
sql
Merge Agent Hangs (reporting no errors)
Hi,
In my merge replication scenario I use Push subscriptions. The
distributor is SQL server 2000 Sp3a, and subscribers are MSDE SP3a.
Everything works fine but recently merge agents started to "hang"
reporting no errors at random phases of replication session. They stop
sending or receiving any data between distributor and subscriber
(there is no network traffic). SQL Profiles show no activity on a
subscriber server. Last message generated by agent is "The process is
running and is waiting for a response from one of the backend
connections".
If there is a problem with communication between servers one would
expect returnig an error code and retrying operation. In my case the
merge agent "hangs" and waits for nothing. After 1 or 2 hours it
reports a communication link failure (waits for a random period of
time).
To continue a Merge process I have to Stop and Start the merge agent
manually.
Since my replication sends a lot of data from subscribers
(MSMerge_genhistory has approx 50000 entries when retention period is
set to 2 days.) after uploading data changes to the publisher (the
procedure sp_MScheckexistsgeneration is executed for every one of
50000 generations - am I right about this?) it takes a long time (40
minutes) to complete. If merge agent hangs, the process must be run
from the beginning. In this situation it is almost impossible to
complete the replication session.
What can be the cause of such behavior of merge agents?
Last lines of output (level 3) generated by agent are as follows:
{call sp_MScheckexistsgeneration (?, ?) }
{call sp_MScheckexistsgeneration (?, ?) }
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend connections.
Repl Agent Status: 3
Tomek
Tomek,
it's difficult to say, but in some sense this looks like a connectivity
issue. However, when I've seen such a situation before, the error was
'General network error'. You might like to verify is this is/is not the
case, by using a Network Monitor tool:
http://support.microsoft.com/default...48942&sd=tech.
Alternatively you could open a window in QA on the publisher, with the
window connected to the subscriber SQL Server. In the window do a simple
select command in a loop and return the date. This should tell you if/when
the connectivity had problems.
Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
if there is any blocking involved. If the problem is not connectivity then
this would be my next test.
I'd also like to know what happens when you restart the agent - does it work
quickly or still take a long time but is successful.
No doubt you have considered this, but if possible can you synchronize more
often to shorten the batchsize?
You might also optimize performance by running
sp_mergemetadataretentioncleanup manually and optimize
he -DownloadGenerationsPerBatch parameter.
HTH,
Paul Ibison
|||Paul,
Thank you for your response. I hope my answers to your questions will
help us to solve the problem.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#EzzqyDQEHA.1312@.TK2MSFTNGP12.phx.gbl>...
> it's difficult to say, but in some sense this looks like a connectivity
> issue. However, when I've seen such a situation before, the error was
> 'General network error'. You might like to verify is this is/is not the
> case, by using a Network Monitor tool:
> http://support.microsoft.com/default...48942&sd=tech.
It can be related to connectivity because merge agents connect to
subscribers that not always use reliable network connection. But the
problem is that instead of reporting General network error and
retrying operation agent waits (hangs) doing nothing.
> Alternatively you could open a window in QA on the publisher, with the
> window connected to the subscriber SQL Server. In the window do a simple
> select command in a loop and return the date. This should tell you if/when
> the connectivity had problems.
QA in loop worked fine (no connectivity problems), but in the same
time agent assigned to the same subscriber hanged.
> Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
> if there is any blocking involved. If the problem is not connectivity then
> this would be my next test.
I've checked this ne too. Blocking is not the reason.
> I'd also like to know what happens when you restart the agent - does it work
> quickly or still take a long time but is successful.
I will describe how my agent works. Please tell me is it correct
behavior.
1) After connecting to subscriber agent uploads all the data changes
made by subscriber to the publisher. This works very fast and i do not
see any problem here (agent has never stopped at this phase).
2) Merge agent executes sp_MScheckexistsgeneration for each of 50000
generations stored in msmerge_genhistory at subscriber side. If there
is some data to download, it is downloaded to subscriber, and then
merge agent checks next generation.
Now let's assume that merge agent "hanged" ot generation 39000. After
restarting the agent it starts the process from the beginning checking
each of 39000 generations that where checked and merged in previus
session.
The merge agent is succesfull only if it is able to go through 50000
generations without loosing connection to subscriber. That is wy it is
so difficult to complete merge session.
IMHO the merge agent should start from generation 39000 making
completing session possible even on not reliable connections. Is it
"by design" behaviour?
> No doubt you have considered this, but if possible can you synchronize more
> often to shorten the batchsize?
It is not possible. subscribers can not synchronize on saturday and
sunday. So 2 days retention perod is minumum.
> You might also optimize performance by running
> sp_mergemetadataretentioncleanup manually and optimize
> he -DownloadGenerationsPerBatch parameter.
I think the problem is not metadata at publisher but metadata at
subscribers. It is not deleted after succesfull replication session (i
think it must work this way).
DownloadGenerationsPerBatch does not help because most of the time
merge agent executes sp_MScheckexistsgeneration without downloading
any data changes. Or maybe I am misunderstanding this parameter?.
Tomek
|||Tomek,
Slow and unreliable connections can result in more retries by the Merge
Agent, none of which you've seen. Your QA test also indicates the same
thing, so we can cross this off the list.
Answers inline...
> IMHO the merge agent should start from generation 39000 making
> completing session possible even on not reliable connections. Is it
> "by design" behaviour?
>
I guess it is not processed this way to try to avoid potential
non-convergence as inserts and child records could be in separate batches
(http://support.microsoft.com/default...b;enus;Q308266)
>
Having MetadataRetentionCleanup 1 should sort this out, but you could run
manually sp_mergemetadataretentioncleanup on the publisher and subscriber to
see if things are improved. It won't remove the 50000 entries, but what I am
hoping is that the search for new generations will be noticeably quicker
after old ones are removed.
> DownloadGenerationsPerBatch does not help because most of the time
> merge agent executes sp_MScheckexistsgeneration without downloading
> any data changes. Or maybe I am misunderstanding this parameter?.
Increasing the -DownloadGenerationsPerBatch Merge Agent parameters (even to
its maximum value of 2000) is a recommendation where a Subscriber has not
merged data with the Publisher for an extended period time, during which the
Publisher or other Subscribers have made numerous changes
(http://www.microsoft.com/technet/pro...n/mergperf.msp
x). So, I'd definitley test altering this parameter's value.
(Powodzenia)
Paul
In my merge replication scenario I use Push subscriptions. The
distributor is SQL server 2000 Sp3a, and subscribers are MSDE SP3a.
Everything works fine but recently merge agents started to "hang"
reporting no errors at random phases of replication session. They stop
sending or receiving any data between distributor and subscriber
(there is no network traffic). SQL Profiles show no activity on a
subscriber server. Last message generated by agent is "The process is
running and is waiting for a response from one of the backend
connections".
If there is a problem with communication between servers one would
expect returnig an error code and retrying operation. In my case the
merge agent "hangs" and waits for nothing. After 1 or 2 hours it
reports a communication link failure (waits for a random period of
time).
To continue a Merge process I have to Stop and Start the merge agent
manually.
Since my replication sends a lot of data from subscribers
(MSMerge_genhistory has approx 50000 entries when retention period is
set to 2 days.) after uploading data changes to the publisher (the
procedure sp_MScheckexistsgeneration is executed for every one of
50000 generations - am I right about this?) it takes a long time (40
minutes) to complete. If merge agent hangs, the process must be run
from the beginning. In this situation it is almost impossible to
complete the replication session.
What can be the cause of such behavior of merge agents?
Last lines of output (level 3) generated by agent are as follows:
{call sp_MScheckexistsgeneration (?, ?) }
{call sp_MScheckexistsgeneration (?, ?) }
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend connections.
Repl Agent Status: 3
Tomek
Tomek,
it's difficult to say, but in some sense this looks like a connectivity
issue. However, when I've seen such a situation before, the error was
'General network error'. You might like to verify is this is/is not the
case, by using a Network Monitor tool:
http://support.microsoft.com/default...48942&sd=tech.
Alternatively you could open a window in QA on the publisher, with the
window connected to the subscriber SQL Server. In the window do a simple
select command in a loop and return the date. This should tell you if/when
the connectivity had problems.
Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
if there is any blocking involved. If the problem is not connectivity then
this would be my next test.
I'd also like to know what happens when you restart the agent - does it work
quickly or still take a long time but is successful.
No doubt you have considered this, but if possible can you synchronize more
often to shorten the batchsize?
You might also optimize performance by running
sp_mergemetadataretentioncleanup manually and optimize
he -DownloadGenerationsPerBatch parameter.
HTH,
Paul Ibison
|||Paul,
Thank you for your response. I hope my answers to your questions will
help us to solve the problem.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#EzzqyDQEHA.1312@.TK2MSFTNGP12.phx.gbl>...
> it's difficult to say, but in some sense this looks like a connectivity
> issue. However, when I've seen such a situation before, the error was
> 'General network error'. You might like to verify is this is/is not the
> case, by using a Network Monitor tool:
> http://support.microsoft.com/default...48942&sd=tech.
It can be related to connectivity because merge agents connect to
subscribers that not always use reliable network connection. But the
problem is that instead of reporting General network error and
retrying operation agent waits (hangs) doing nothing.
> Alternatively you could open a window in QA on the publisher, with the
> window connected to the subscriber SQL Server. In the window do a simple
> select command in a loop and return the date. This should tell you if/when
> the connectivity had problems.
QA in loop worked fine (no connectivity problems), but in the same
time agent assigned to the same subscriber hanged.
> Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
> if there is any blocking involved. If the problem is not connectivity then
> this would be my next test.
I've checked this ne too. Blocking is not the reason.
> I'd also like to know what happens when you restart the agent - does it work
> quickly or still take a long time but is successful.
I will describe how my agent works. Please tell me is it correct
behavior.
1) After connecting to subscriber agent uploads all the data changes
made by subscriber to the publisher. This works very fast and i do not
see any problem here (agent has never stopped at this phase).
2) Merge agent executes sp_MScheckexistsgeneration for each of 50000
generations stored in msmerge_genhistory at subscriber side. If there
is some data to download, it is downloaded to subscriber, and then
merge agent checks next generation.
Now let's assume that merge agent "hanged" ot generation 39000. After
restarting the agent it starts the process from the beginning checking
each of 39000 generations that where checked and merged in previus
session.
The merge agent is succesfull only if it is able to go through 50000
generations without loosing connection to subscriber. That is wy it is
so difficult to complete merge session.
IMHO the merge agent should start from generation 39000 making
completing session possible even on not reliable connections. Is it
"by design" behaviour?
> No doubt you have considered this, but if possible can you synchronize more
> often to shorten the batchsize?
It is not possible. subscribers can not synchronize on saturday and
sunday. So 2 days retention perod is minumum.
> You might also optimize performance by running
> sp_mergemetadataretentioncleanup manually and optimize
> he -DownloadGenerationsPerBatch parameter.
I think the problem is not metadata at publisher but metadata at
subscribers. It is not deleted after succesfull replication session (i
think it must work this way).
DownloadGenerationsPerBatch does not help because most of the time
merge agent executes sp_MScheckexistsgeneration without downloading
any data changes. Or maybe I am misunderstanding this parameter?.
Tomek
|||Tomek,
Slow and unreliable connections can result in more retries by the Merge
Agent, none of which you've seen. Your QA test also indicates the same
thing, so we can cross this off the list.
Answers inline...
> IMHO the merge agent should start from generation 39000 making
> completing session possible even on not reliable connections. Is it
> "by design" behaviour?
>
I guess it is not processed this way to try to avoid potential
non-convergence as inserts and child records could be in separate batches
(http://support.microsoft.com/default...b;enus;Q308266)
>
Having MetadataRetentionCleanup 1 should sort this out, but you could run
manually sp_mergemetadataretentioncleanup on the publisher and subscriber to
see if things are improved. It won't remove the 50000 entries, but what I am
hoping is that the search for new generations will be noticeably quicker
after old ones are removed.
> DownloadGenerationsPerBatch does not help because most of the time
> merge agent executes sp_MScheckexistsgeneration without downloading
> any data changes. Or maybe I am misunderstanding this parameter?.
Increasing the -DownloadGenerationsPerBatch Merge Agent parameters (even to
its maximum value of 2000) is a recommendation where a Subscriber has not
merged data with the Publisher for an extended period time, during which the
Publisher or other Subscribers have made numerous changes
(http://www.microsoft.com/technet/pro...n/mergperf.msp
x). So, I'd definitley test altering this parameter's value.
(Powodzenia)
Paul
Merge agent failure; Urgent
Hi,
We have a publisher and 2 subsribers. The subscribers are spanning 2
transactions/sec. We have inplemented merge replication and it was
scheduled to occur every 5 min. When we start the replication, the
replications is going on without any problem for sometime(may be some
hours), but after that it fails with the message, "the process could
not deliver inserts at the publisher". That too we have 3 articles to
replicate. The first article is replicating fine.But the second article
fails with this error message "the process could not deliver inserts at
the publisher".We have SAN. When I searched for this error in google,
it asked me to increase the queytimeout value in agent profile. So I
increased it from 300 to 600, but in vain. If anybody could help me in
this, that would be great.
Thanks,
Preethi.
Preethi,
perhaps you could further increase the timeout? I'd also implement logging
(http://support.microsoft.com/?id=312292). After that, have a look at the
spid using dbcc inputbuffer to see what is happening on synchronization. You
might also want to run sp_who2 to look for any blocking occurring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
We have a publisher and 2 subsribers. The subscribers are spanning 2
transactions/sec. We have inplemented merge replication and it was
scheduled to occur every 5 min. When we start the replication, the
replications is going on without any problem for sometime(may be some
hours), but after that it fails with the message, "the process could
not deliver inserts at the publisher". That too we have 3 articles to
replicate. The first article is replicating fine.But the second article
fails with this error message "the process could not deliver inserts at
the publisher".We have SAN. When I searched for this error in google,
it asked me to increase the queytimeout value in agent profile. So I
increased it from 300 to 600, but in vain. If anybody could help me in
this, that would be great.
Thanks,
Preethi.
Preethi,
perhaps you could further increase the timeout? I'd also implement logging
(http://support.microsoft.com/?id=312292). After that, have a look at the
spid using dbcc inputbuffer to see what is happening on synchronization. You
might also want to run sp_who2 to look for any blocking occurring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
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
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
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
sqlmerge 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
Merge agent error
We have set up a merge publication which has about 34 subscribers. Suddenly
when i try to add a new subscriber, everything works fine until i start the
merge agent on the subscriber.
I get the error : Publication 'xxx' does not exist
Invalid cursor position.
I have included a part of the agent logging (verboselogging 2)
Start logging --
Microsoft SQL Server Merge Agent 8.00.382
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
Percent Complete: 0
Connecting to Subscriber 'BNM'
Connecting to Subscriber 'BNM.CRM'
Server: BNM
DBMS: Microsoft SQL Server
Version: 08.00.0384
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Distributor security mode: 0, login name: BNM, password: ********.
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Publisher security mode: 0, login name: BNM, password: bn159.
Percent Complete: 0
Connecting to Distributor 'SQLSRV'
Connecting to Distributor 'SQLSRV.'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
[4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SQLSRV')
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'BNM')
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
N'CRM', N'BOOSTmerge', 33, N'CRM')}
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4, N'')}
Percent Complete: 0
Connecting to Publisher 'SQLSRV.CRM'
Initializing
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.CRM: set nocount on declare @.dbname sysname select
@.dbname = db_name() declare @.collation nvarchar(255) select @.collation =
convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Connecting to Publisher 'SQLSRV.CRM'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Publication 'BOOSTmerge' does not exist.
Percent Complete: 1
Connecting to Publisher 'SQLSRV'
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147201022
Message: Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Category:SQLSERVER
Source: SQLSRV
Number: 0
Message: Invalid cursor state
Disconnecting from Subscriber 'BNM'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Distributor 'SQLSRV'
On the server SQLSRV can you run sp_helppublication to ensure that
BOOSTmerge exists.
Rgds,
Paul Ibison
"Nol Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
> We have set up a merge publication which has about 34 subscribers.
> Suddenly
> when i try to add a new subscriber, everything works fine until i start
> the
> merge agent on the subscriber.
> I get the error : Publication 'xxx' does not exist
> Invalid cursor position.
> I have included a part of the agent logging (verboselogging 2)
> Start logging --
> Microsoft SQL Server Merge Agent 8.00.382
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
> Percent Complete: 0
> Connecting to Subscriber 'BNM'
> Connecting to Subscriber 'BNM.CRM'
> Server: BNM
> DBMS: Microsoft SQL Server
> Version: 08.00.0384
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Distributor security mode: 0, login name: BNM, password: ********.
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Publisher security mode: 0, login name: BNM, password: bn159.
> Percent Complete: 0
> Connecting to Distributor 'SQLSRV'
> Connecting to Distributor 'SQLSRV.'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
> [4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'SQLSRV')
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'BNM')
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
> N'CRM', N'BOOSTmerge', 33, N'CRM')}
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4,
> N'')}
> Percent Complete: 0
> Connecting to Publisher 'SQLSRV.CRM'
> Initializing
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.CRM: set nocount on declare @.dbname sysname
> select
> @.dbname = db_name() declare @.collation nvarchar(255) select @.collation =
> convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
> collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
> collationproperty(@.collation, N'LCID') as 'LCID',
> collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
> Connecting to Publisher 'SQLSRV.CRM'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 1
> Connecting to Publisher 'SQLSRV'
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147201022
> Message: Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Category:SQLSERVER
> Source: SQLSRV
> Number: 0
> Message: Invalid cursor state
> Disconnecting from Subscriber 'BNM'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Distributor 'SQLSRV'
>
|||I have tried this procedure and it runs correctly. My opinion is that it has
something todo with data which is not cleaned up correctly.
I had the same subscriber running before on the same publication, but the
machine needed to be re?nstalled after a disc crash.
When i try to subscribe now, i get this error.
Another weird thing is that adding the subscribtion works fine, but in the
list of all the subscribers, the subscription has status deactivated. Even
after running the snapshot agent.
"Paul Ibison" wrote:
> On the server SQLSRV can you run sp_helppublication to ensure that
> BOOSTmerge exists.
> Rgds,
> Paul Ibison
> "No?l Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
> news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
>
>
|||I can't tell what happened when the disk crashed and the
machine was rebuilt. Somehow the subscriber details on
the publisher seem to refer to the old subscription, and
have been deactivated presumably because of not
synchronizing. I'd drop the subscription/subscriber, make
sure that the builds are done to the correct service pack
level, perhaps even reapply sp3a on the subscriber if
there's any doubt, then add the subscriber/subscription
from scratch.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
when i try to add a new subscriber, everything works fine until i start the
merge agent on the subscriber.
I get the error : Publication 'xxx' does not exist
Invalid cursor position.
I have included a part of the agent logging (verboselogging 2)
Start logging --
Microsoft SQL Server Merge Agent 8.00.382
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
Percent Complete: 0
Connecting to Subscriber 'BNM'
Connecting to Subscriber 'BNM.CRM'
Server: BNM
DBMS: Microsoft SQL Server
Version: 08.00.0384
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Distributor security mode: 0, login name: BNM, password: ********.
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Publisher security mode: 0, login name: BNM, password: bn159.
Percent Complete: 0
Connecting to Distributor 'SQLSRV'
Connecting to Distributor 'SQLSRV.'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
[4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SQLSRV')
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'BNM')
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
N'CRM', N'BOOSTmerge', 33, N'CRM')}
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4, N'')}
Percent Complete: 0
Connecting to Publisher 'SQLSRV.CRM'
Initializing
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.CRM: set nocount on declare @.dbname sysname select
@.dbname = db_name() declare @.collation nvarchar(255) select @.collation =
convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Connecting to Publisher 'SQLSRV.CRM'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Publication 'BOOSTmerge' does not exist.
Percent Complete: 1
Connecting to Publisher 'SQLSRV'
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147201022
Message: Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Category:SQLSERVER
Source: SQLSRV
Number: 0
Message: Invalid cursor state
Disconnecting from Subscriber 'BNM'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Distributor 'SQLSRV'
On the server SQLSRV can you run sp_helppublication to ensure that
BOOSTmerge exists.
Rgds,
Paul Ibison
"Nol Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
> We have set up a merge publication which has about 34 subscribers.
> Suddenly
> when i try to add a new subscriber, everything works fine until i start
> the
> merge agent on the subscriber.
> I get the error : Publication 'xxx' does not exist
> Invalid cursor position.
> I have included a part of the agent logging (verboselogging 2)
> Start logging --
> Microsoft SQL Server Merge Agent 8.00.382
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
> Percent Complete: 0
> Connecting to Subscriber 'BNM'
> Connecting to Subscriber 'BNM.CRM'
> Server: BNM
> DBMS: Microsoft SQL Server
> Version: 08.00.0384
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Distributor security mode: 0, login name: BNM, password: ********.
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Publisher security mode: 0, login name: BNM, password: bn159.
> Percent Complete: 0
> Connecting to Distributor 'SQLSRV'
> Connecting to Distributor 'SQLSRV.'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
> [4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'SQLSRV')
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'BNM')
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
> N'CRM', N'BOOSTmerge', 33, N'CRM')}
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4,
> N'')}
> Percent Complete: 0
> Connecting to Publisher 'SQLSRV.CRM'
> Initializing
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.CRM: set nocount on declare @.dbname sysname
> select
> @.dbname = db_name() declare @.collation nvarchar(255) select @.collation =
> convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
> collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
> collationproperty(@.collation, N'LCID') as 'LCID',
> collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
> Connecting to Publisher 'SQLSRV.CRM'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 1
> Connecting to Publisher 'SQLSRV'
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147201022
> Message: Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Category:SQLSERVER
> Source: SQLSRV
> Number: 0
> Message: Invalid cursor state
> Disconnecting from Subscriber 'BNM'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Distributor 'SQLSRV'
>
|||I have tried this procedure and it runs correctly. My opinion is that it has
something todo with data which is not cleaned up correctly.
I had the same subscriber running before on the same publication, but the
machine needed to be re?nstalled after a disc crash.
When i try to subscribe now, i get this error.
Another weird thing is that adding the subscribtion works fine, but in the
list of all the subscribers, the subscription has status deactivated. Even
after running the snapshot agent.
"Paul Ibison" wrote:
> On the server SQLSRV can you run sp_helppublication to ensure that
> BOOSTmerge exists.
> Rgds,
> Paul Ibison
> "No?l Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
> news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
>
>
|||I can't tell what happened when the disk crashed and the
machine was rebuilt. Somehow the subscriber details on
the publisher seem to refer to the old subscription, and
have been deactivated presumably because of not
synchronizing. I'd drop the subscription/subscriber, make
sure that the builds are done to the correct service pack
level, perhaps even reapply sp3a on the subscriber if
there's any doubt, then add the subscriber/subscription
from scratch.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Friday, March 23, 2012
Merge - Pull Snapshot Error
I'm struggling with this error with one of my subscribers. Both the
publisher and subscriber have the services running under the same account &
password. My subscribers use a dial-up connection. To help
troubleshoot, I had the subscriber get connected to the publisher via RAS
and had them run a directory listing on the snapshot location. They
received an error: Logon failure: unknown user name or bad password.
The RAS user account has full access to the snapshot location. This is the
first subscriber out of 15 that I'm experiencing this problem with. The
difference between this one and the other 14 is that this subscriber is
running a full version of SQL Server where as the rest are running MSDE.
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
The process could not bulk copy into table '"dbo"."sysmergesubsetfilters"'.
(Source: cookieland (Agent); Error number: 20037)
Unable to open BCP host data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
This is not a replication problem. It is a problem relating to network connectivity or RAS.
Unfortunately my NT/RAS/dialup skills aren't good enough to help you. I suggest you open a ticket with PSS for Win2k networking, or post this question in one of the networking newsgroups.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" wrote:
> I'm struggling with this error with one of my subscribers. Both the
> publisher and subscriber have the services running under the same account &
> password. My subscribers use a dial-up connection. To help
> troubleshoot, I had the subscriber get connected to the publisher via RAS
> and had them run a directory listing on the snapshot location. They
> received an error: Logon failure: unknown user name or bad password.
> The RAS user account has full access to the snapshot location. This is the
> first subscriber out of 15 that I'm experiencing this problem with. The
> difference between this one and the other 14 is that this subscriber is
> running a full version of SQL Server where as the rest are running MSDE.
> The process could not deliver the snapshot to the Subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ----
> The process could not bulk copy into table '"dbo"."sysmergesubsetfilters"'.
> (Source: cookieland (Agent); Error number: 20037)
> ----
> Unable to open BCP host data-file
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
>
>
|||Thanks Hilary. I have since learned the credentials are cached in XP and
used when you try to access any
resources in the remote network. The same feature is not present pre XP.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:8BE474D2-F7A1-414E-9B35-5DD6239B8833@.microsoft.com...
> This is not a replication problem. It is a problem relating to network
connectivity or RAS.
> Unfortunately my NT/RAS/dialup skills aren't good enough to help you. I
suggest you open a ticket with PSS for Win2k networking, or post this
question in one of the networking newsgroups.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Tina Smith" wrote:
account &[vbcol=seagreen]
RAS[vbcol=seagreen]
the[vbcol=seagreen]
The
> ----
--[vbcol=seagreen]
'"dbo"."sysmergesubsetfilters"'.
> ----
--[vbcol=seagreen]
publisher and subscriber have the services running under the same account &
password. My subscribers use a dial-up connection. To help
troubleshoot, I had the subscriber get connected to the publisher via RAS
and had them run a directory listing on the snapshot location. They
received an error: Logon failure: unknown user name or bad password.
The RAS user account has full access to the snapshot location. This is the
first subscriber out of 15 that I'm experiencing this problem with. The
difference between this one and the other 14 is that this subscriber is
running a full version of SQL Server where as the rest are running MSDE.
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
The process could not bulk copy into table '"dbo"."sysmergesubsetfilters"'.
(Source: cookieland (Agent); Error number: 20037)
Unable to open BCP host data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
This is not a replication problem. It is a problem relating to network connectivity or RAS.
Unfortunately my NT/RAS/dialup skills aren't good enough to help you. I suggest you open a ticket with PSS for Win2k networking, or post this question in one of the networking newsgroups.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" wrote:
> I'm struggling with this error with one of my subscribers. Both the
> publisher and subscriber have the services running under the same account &
> password. My subscribers use a dial-up connection. To help
> troubleshoot, I had the subscriber get connected to the publisher via RAS
> and had them run a directory listing on the snapshot location. They
> received an error: Logon failure: unknown user name or bad password.
> The RAS user account has full access to the snapshot location. This is the
> first subscriber out of 15 that I'm experiencing this problem with. The
> difference between this one and the other 14 is that this subscriber is
> running a full version of SQL Server where as the rest are running MSDE.
> The process could not deliver the snapshot to the Subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ----
> The process could not bulk copy into table '"dbo"."sysmergesubsetfilters"'.
> (Source: cookieland (Agent); Error number: 20037)
> ----
> Unable to open BCP host data-file
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
>
>
|||Thanks Hilary. I have since learned the credentials are cached in XP and
used when you try to access any
resources in the remote network. The same feature is not present pre XP.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:8BE474D2-F7A1-414E-9B35-5DD6239B8833@.microsoft.com...
> This is not a replication problem. It is a problem relating to network
connectivity or RAS.
> Unfortunately my NT/RAS/dialup skills aren't good enough to help you. I
suggest you open a ticket with PSS for Win2k networking, or post this
question in one of the networking newsgroups.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Tina Smith" wrote:
account &[vbcol=seagreen]
RAS[vbcol=seagreen]
the[vbcol=seagreen]
The
> ----
--[vbcol=seagreen]
'"dbo"."sysmergesubsetfilters"'.
> ----
--[vbcol=seagreen]
Subscribe to:
Comments (Atom)