SQL Server Performance Forum – Threads Archive

Transactional Replication & Bulk Updates

I have a 8 tables replicated with a pull subscription on SQL 2000 and this has been working fine. Unfortunately the system is rather open and last week the users of the system managed to cause an update to 100000 records (about half the table) at the publisher on a table with about 100 columns. These records then took a day to replicate (that wasn’t helped by an On Update Trigger at the subscriber), during which time the subscriber table became progressively more locked up (using Key level locks) and so my web site that looks at the Subscriber database became unusable. Give that we need near instant replication of data & I can’t guarantee the users wont do a bulk update again is there any way to help these records across faster.

http://www.sql-server-performance.com/transactional_replication.asp &http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx for tips and information. http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm on bulk copy optimization.(contributed by LazyDBA on other thread).
HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Cheers for that, I’m having a look through those sites now, but I think I forgot to say before that there isn’t a problem with the bulk update on the publisher. I’m not sure exactly how long the update took to run at the publisher, but I reckon it was only a minute.
The Publisher database continues to work fine. The issue is that it calles the update proc on the subscriber 100000 times within one transaction, and each time the proc is run it takes a handfull of Key Level locks. Towards the end I had millions of locks (ran sp_lock). I wasn’t sure if I could get it to perform the replication in some other way?

Review the information listed on the first link to fine tune the replication. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>