deadlock with replication's update/insert/delete process

We are using SQL 2K with sp4. We are using Push method and both publisher
and distribution db are on the same server.
Recently we have quite a few deadlocks involving a replication's sp such as
sp_MSupd_xxxxxxx and some select statement from our application. We have
deadlocks from time to time but I haven't seen a deadlock caused by sp_MSupd
until now. Is this an indication of something that I need to look into?
Any help is very much appreciated.
Wingman
Sounds like a transaction on the publisher is coming over as a transaction
on the subscriber which you're deadlocking with. Some ideas: you could look
at the order of the transaction on the publisher and check that your
application accesses tables in the same order. You could allow your
application to do dirty reads (not generally recommended but sometimes
useful). You could use SQL Server 2005 and the snapshot isolation level
where this problem goes away. Finally you could control when the
distribution agent runs to avoid the lcash.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

deadlock errors

I'm setting up a push merge replication, after the initial merge agent completes without errors I move the subscriber from a wired connection to a wireless connection. At this point I get an error…

The schema script …Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\[table]_93.sch' could not be propagated to the subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

Cannot drop the table '[table]' because it is being used for replication.

(Source: dfdmfvltgh09 (Data source); Error number: 3724)

After this error the initial merge agent kicks off again trying to apply the initial snapshot where I then get the following deadlock errors.

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

(Source: dfdmfvltgh09 (Data source); Error number: 1205)

The process could not deliver the snapshot to the Subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

All are getting set up on sql 2000 sp4 on a push.We are trying to set up 10 units that all are pointing to the same dlink router model dwl-2100AP.5 are working the other 5 are coming up with this error.Anybody have any ideas what could be causing this.I don’t mind the getting cut off due to poor network connectivity, I really want to stop the initial merge agent from kicking off after it’s already completed.

Thank you in advanced,

Pauly C

Let's tackle the snapshot failure first. Are you saying merge is trying to apply the snapshot twice, once on the wired network and once on the wireless network? Are these two networks on the same domain?

|||

Yes, the snapshot completes without errors, and replication works, changes get propergated back and forth between the subscriber and publisher, then when they move the subscriber to a wireless connection the first error occurs and the initial merge agent starts again.After applying some of the snapshot it gets the deadlock error. and it keeps trying to apply the initial merge agaent over and over.

|||And yes they are on the same domain. Somebody told me they were able to ping a machine that wasn't connected and actually got results. I did not see this with my own eyes so I'm not sure how true it is.

Deactivated?

Merge replication.
Push New Subscription.
Immediately after I click "finish" in the wizard (push new subscription),
the subscription status shows deactivated. The merge agent runs and
completes then shows status has failed. error = "the subscription to the
publication pub_name is invalid. The remote-server is not defined as a
subscription server. 14010"
Your problem is that the remote server is not enabled as a subscriber. I'm
not trying to be flippant, but this might take a little back and forth to
fix.
First off - all publications/subscriptions are marked as deactivated until
the snapshot agent runs. So this status is not problematic in your case.
To enable your subscription server as a subscriber, go to tools,
Replication, Configure Distributor, Publishers, and Subscribers, click on
the subscriber tab, locate your subscriber or register it.
You might also want to review this pdf -
http://www.nwsu.com/lowres_replication_ch02.pdf
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:%23cKdOIoAFHA.904@.TK2MSFTNGP12.phx.gbl...
> Merge replication.
> Push New Subscription.
> Immediately after I click "finish" in the wizard (push new subscription),
> the subscription status shows deactivated. The merge agent runs and
> completes then shows status has failed. error = "the subscription to the
> publication pub_name is invalid. The remote-server is not defined as a
> subscription server. 14010"
>