Merge Agent Failure

Hello,
We've been using merge replication now in a live customer environment for
several months with only one major issue; every several days to several
times a day, the merge agent fails with the following error message:
The process could not enumerate changes at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
Transaction (Process ID 152) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
(Source: ABC-DB2 (Data source); Error number: 1205)
Sometimes the agent fails with the message "The process could not enumerate
changes at the 'Subscriber'." but other than that, it's the same every
single time. We have found that each time this occurs, we can simply
restart the agent and it starts up just fine. We have even gone so far as
to put a job in place at each customer site to restart the merge agent upon
failure, but our application must be able to handle this failure as well
which requires more work arounds.
Is there any way to solve this problem? At this point, just through trial
and error, we've reduced the frequency of the failure by setting
NumDeadlockRetries to it's maximum, reduced SrcThreads and DestThreads to
their minimum, set both UploadGenerationsPerBatch and
DownloadGenerationsPerBatch to 2000, QueryTimeout to 300 and PollingInterval
to 60 but we cannot eliminate the error.
I see Hilary's comment on 11/21 that these errors are normally transitory
and to just restart the merge agent, but this is not so simple for us in our
environment. What we really need to do is stop the problem from happening.
What is causing this error? Is there any way to get completely rid of this
problem? Does anyone know if the problem has been solved in SQL Server
2005?
Thanks in advance for any help you might be able to provide.
Thanks,
Marshall
I'd try monitoring for deadlocks. This can be donw through profiler
(particluarly well in SQl Server 2005 with the deadlock graphs), or using
trace flags. DBCC TRACEON(1204,1205,3605,-1) on the publisher then run the
merge agent. Check SQL Server logs for the deadlock information. It isn't in
especailly well-formatted style but the TSQL causing the problems can be
gained ths way. Once you've got the info, you just need to turn off the trace
flags.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There are many sources of this error. One of them could be too many
concurrently running merge agents. Another could be the complexity of your
join filters.
How many subscribers do you have? On what criteria are you filtering?
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
"Marshall" <MBCDev> wrote in message
news:gsudna7sf6fG6gDenZ2dnUVZ_vydnZ2d@.wideopenwest .com...
> Hello,
> We've been using merge replication now in a live customer environment for
> several months with only one major issue; every several days to several
> times a day, the merge agent fails with the following error message:
> The process could not enumerate changes at the 'Publisher'.
> (Source: Merge Replication Provider (Agent); Error number: -2147200999)
> ----
> --
> Transaction (Process ID 152) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
> (Source: ABC-DB2 (Data source); Error number: 1205)
> ----
> --
> Sometimes the agent fails with the message "The process could not
> enumerate
> changes at the 'Subscriber'." but other than that, it's the same every
> single time. We have found that each time this occurs, we can simply
> restart the agent and it starts up just fine. We have even gone so far as
> to put a job in place at each customer site to restart the merge agent
> upon
> failure, but our application must be able to handle this failure as well
> which requires more work arounds.
> Is there any way to solve this problem? At this point, just through trial
> and error, we've reduced the frequency of the failure by setting
> NumDeadlockRetries to it's maximum, reduced SrcThreads and DestThreads to
> their minimum, set both UploadGenerationsPerBatch and
> DownloadGenerationsPerBatch to 2000, QueryTimeout to 300 and
> PollingInterval
> to 60 but we cannot eliminate the error.
> I see Hilary's comment on 11/21 that these errors are normally transitory
> and to just restart the merge agent, but this is not so simple for us in
> our
> environment. What we really need to do is stop the problem from
> happening.
> What is causing this error? Is there any way to get completely rid of
> this
> problem? Does anyone know if the problem has been solved in SQL Server
> 2005?
> Thanks in advance for any help you might be able to provide.
>
> Thanks,
> Marshall
>
|||Hello Hilary,
We have only one merge agent running with one subscriber to the publisher.
Also, we do no filtering whatsoever - it's purely table to table
replication, but on all tables (over 700) in the database. I just read
Paul's suggestion on trying to research the cause of the deadlock itself. I
will give that a try to see if I can find any additional information.
Thanks,
Marshall
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uLGDjxy$FHA.3852@.TK2MSFTNGP14.phx.gbl...
> There are many sources of this error. One of them could be too many
> concurrently running merge agents. Another could be the complexity of your
> join filters.
> How many subscribers do you have? On what criteria are you filtering?
> --
> 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
> "Marshall" <MBCDev> wrote in message
> news:gsudna7sf6fG6gDenZ2dnUVZ_vydnZ2d@.wideopenwest .com...
>
|||Thanks Paul,
I will give this a try and see if I can find out anything. I will
definitely let you and the rest of the newsgroup know of anything we
discover.
Thanks again,
Marshall
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0A4FA093-B706-4353-AA2C-25972E2DD7DD@.microsoft.com...
> I'd try monitoring for deadlocks. This can be donw through profiler
> (particluarly well in SQl Server 2005 with the deadlock graphs), or using
> trace flags. DBCC TRACEON(1204,1205,3605,-1) on the publisher then run the
> merge agent. Check SQL Server logs for the deadlock information. It isn't
> in
> especailly well-formatted style but the TSQL causing the problems can be
> gained ths way. Once you've got the info, you just need to turn off the
> trace
> flags.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I have the same problem and have been dealing with that for months without
any solution. But I didn't have this problem until I applied a hotfix that
Microsoft gave me. I did the trace and I know in what table I'm having the
deadlock. I opened a case with Microsoft and after trying and trying
thousands of things they were not able to help and said I need to change the
run continuously parameter to make replication work every some minutes. But
for the conditions of the business we can't do this change, we need to run
it continuously and here I'm dealing with this problem. I hope you have
better lock. Now I'm use to wake up in the middle of the night several times
to start the replication so the business process can continue.
Thanks
"Marshall" <MBCDev> wrote in message
news:gsudna7sf6fG6gDenZ2dnUVZ_vydnZ2d@.wideopenwest .com...
> Hello,
> We've been using merge replication now in a live customer environment for
> several months with only one major issue; every several days to several
> times a day, the merge agent fails with the following error message:
> The process could not enumerate changes at the 'Publisher'.
> (Source: Merge Replication Provider (Agent); Error number: -2147200999)
> ----
> --
> Transaction (Process ID 152) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
> (Source: ABC-DB2 (Data source); Error number: 1205)
> ----
> --
> Sometimes the agent fails with the message "The process could not
> enumerate
> changes at the 'Subscriber'." but other than that, it's the same every
> single time. We have found that each time this occurs, we can simply
> restart the agent and it starts up just fine. We have even gone so far as
> to put a job in place at each customer site to restart the merge agent
> upon
> failure, but our application must be able to handle this failure as well
> which requires more work arounds.
> Is there any way to solve this problem? At this point, just through trial
> and error, we've reduced the frequency of the failure by setting
> NumDeadlockRetries to it's maximum, reduced SrcThreads and DestThreads to
> their minimum, set both UploadGenerationsPerBatch and
> DownloadGenerationsPerBatch to 2000, QueryTimeout to 300 and
> PollingInterval
> to 60 but we cannot eliminate the error.
> I see Hilary's comment on 11/21 that these errors are normally transitory
> and to just restart the merge agent, but this is not so simple for us in
> our
> environment. What we really need to do is stop the problem from
> happening.
> What is causing this error? Is there any way to get completely rid of
> this
> problem? Does anyone know if the problem has been solved in SQL Server
> 2005?
> Thanks in advance for any help you might be able to provide.
>
> Thanks,
> Marshall
>