We have an SQL2000 database server that uses merge, pull replication with about 70 clients around the country, each with their own publication. Each article in each publication is row filtered by a single condition. There are busy times of the day when all of these clients attempt to replicate within a 15-20 minute period. We have noticed the following error that is creating conflicts that are resulting in data not being transferred to the server and very long replication run times (error message is paraphrased):

The record was inserted at the client, but could not be inserted at the server. Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Is there a way to lessen the effects of this error How can I reduce the number of deadlocks that are occurring We will need to be adding a great deal more clients to the system in the future; is there a way to grow the system without making the deadlock problem worse

Any help will be appreciated!



Re: SQL2000 Replication Deadlocks

Greg Y

you'll need to look at the profiler trace to see what exactly is deadlocking. You may want to do more testing before going live with more clients, merge replication in SQL 2000 is known to not be very scalable, but has been greatly improved in SQL 2005.