Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates statements every morning which is causing massive deadlock chains on the subscribers.

I think the resolution is to change this to replicate just a stored procedure call? Do I simply have to add the new stored procedure into the publication & add the article at the subscriber ?

I have attempted this as still seem to be transmitting all of the update statements, I have added the article properties to replicate the call of the stored procedure as well but still seeing all rows in replication monitor

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

Panic over !

When adding the stored procedure to the Article, in the properties there is a setting called 'replicate'. By default this is schema only. When i changed this to Execution of the stored procedure' instead of 'Execution in a serialized transaction of the SP'. and restarted all the agents, it worked as one transaction this time

share|improve this answer
1  
You cannot amend these settings of articles either, you must do it at creation time, or drop and add at a later date, so be careful as this will invalidate your snapshot –  DamagedGoods Aug 8 '12 at 13:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.