Merge Issue - waiting for a response from one of the backend connections

Question

Merge Issue - waiting for a response from one of the backend connections

Barbara-167570

SSCrazy

Points: 2352
More actions
April 12, 2006 at 7:44 am

#91626

Good Morning Everyone,

First the formalities:

System Information:

SQL 2000 - 8.00.760 - SP3, Enterprise Edition
Cluster Environment
Running Continuous Merge replication, with one subscriber.
24*7 web application
Database Size Currently: 6 Gig (and growing)

Now the issue:

We previously had Merge replication running with one subscriber. This past weekend I broke replication as we merged two of our databases together. I recreated replication with this newly created database, did not send the snapshot as the subscriber already had the data. Replication seemed to work fine for approximately 48 hours. But now we are constantly receiving the error message:

"The process is running and is waiting for a response from one of the backend connections"

after a while we then receive the

"Agent is Suspect" error message.

I've scoured the internet for this message and I've seen some recommendations from Hilary Cotter and Paul Ibison. I've increased the merge agents QueryTimeout from our default of 600 to 4000. I've also increased DownloadGenerationsPerBatch from 100 to 1000 however; still experiencing the same issues.

I also read that it may be the Merge tables are large and I need to manually run sp_mergemetadataretetioncleanup but since replication has only been running since Saturday these tables are currently not that big currently:

MSmerge_contents 12,470
MSmerge_genhistory 3,085
MSmerge_tombstone 1,285

I know that in the past these tables have been much larger.

There were some issues with our network but our SysAdmin says that it has now been resolved. One other thing I found online is that you receive this message because indexes are being applied to the subscriber, does that happen every time any updates, inserts, or deletes are performed? I also saw a recommendation to check sp_who2/sp_lock/sp_blocker and I do not see any blocking or locks.

I did notice that the indexes that the system creates for the rowguid columns DO NOT exist on the Subscriber. Are they supposed to be on the subscriber as well? I've never manually created them on the subscriber in the past. Should I script out the ones on the Publisher database and recreate them on the subscriber???

I'm at a loss now for what to do next and patience is wearing thin here if you know what I mean. Any and all advice, suggestions, help will be greatly appreciated.

Thank you very much,

Barbara

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

J Woo SSC Journeyman Points: 79 More actions · Answer 1

I'm not as familiar with merge replication, but you can do the following. Usually that error message is timeout related (basically something is taking too long, and not reporting back to the agent within the timeout params). Use a trace on the subscriber that is timing out, and see what commands are being issued. It could very well be that index(es) you were referring to.

J Woo SSC Journeyman Points: 79 More actions · Answer 2

A quick follow-up...

I setup merge replication, and looked at the procs that are used for the replication. it does use rowguid, so you want those indexes...

Good luck.

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 3

J.Woo,

Thanks for your help, I'm setting up the indexes now.

I'll update the post once I test it.

Thanks again,

Barbara

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 4

J.Woo,

Right now it appears that may have resolved the issues. I'm keeping my finger crossed.

I'm curious as to why those indexes were not automatically created though. I've had this replication running for almost 5 years and I've never had to manually create the rowguid indexes on the subscriber in the past.

Makes you go hmmmmm.

Thanks for your help. I'll post an update later.

Barbara

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 5

Incase anyone is watching this topic replication still appears to be running normally.

Barbara

J Woo SSC Journeyman Points: 79 More actions · Answer 6

J Woo

SSC Journeyman

Points: 79

April 18, 2006 at 12:41 pm

#632943

That's good news...

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 7

It is good news but I wonder why they did not automatically get created as they must have in the past. As I mentioned above I've had this replication running for almost 5 years and I've never had to manually create the rowguid indexes on the subscriber in the past.

Anyone have any ideas??? I'd love to hear them!

Thanks,

Barbara

schwizla SSCrazy Points: 2668 More actions · Answer 8

Hi Barbara,

I am having this very same problem! It is very frustrating. Can you tell me what tables you created the indexes on?

Siz

soulfly SSC Journeyman Points: 97 More actions · Answer 9

when sql reports an error regarding suspect status it means the 2 servers are not properly communicating. try to add the ip of the subscriber to the publisher hosts and also the client connectivity.

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 10

Siz: I created the indexes on the rowguid columns on the subscriber. - B

Soulfly: Thanks for taking time to respond. I know that the suspect error means the servers are not communicating properly. However the lingering question now is why were the rowguid indexes not automatically created on the Subscriber like they had been in the past. Not a pressing issue anymore for me but something that makes you go hmmmmm.

B

noreply-986543 Newbie Points: 7 More actions · Answer 11

did this work for you. I am having the same problem. ???

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 12

Hi,

The only time this has happened is when we have applied a no synch snapshot. We synch the data manually with the rowguids I might add as they are indeed required. When we applied the subscriber with nosynch, no objects are applied..we forgot to add the indexes...but that was the only instance.

Indexes must appear on all the rowguid columns of all the tables used in the merge.

Now we create the snapshot locally, copy to a new location and get the merge agent to apply the snapshot from an alternative location...seems to work fine.

Regards

Graeme