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.

We have setup data transactional replication from Server-A(SQL Server 2008 R2) to Server-B(SQL Server 2008 R2). We have a SQL Job scheduled to run everyday on Server-A, which executes single UPDATE command which causes ~120K record updates on a specific table. Now the problem here is, when that single UPDATE command gets executed on Server-A it updates nearly 120K records within few seconds. This is a single transaction on Server-A but it is equivalent to ~120K transactions on Server-B. This causes, the table on Server-B gets lock down for more than 20-30 min. everyday, whenever that SQL Job runs.

I read about MaxCmdsInTran option, but it isn't a recommended one, so cant use it. As an alternate option, we are planning to exclude that particular table from Data Replication and we are planning to execute that SQL Job on both Server-A and Server-B.

I am just wondering if there is any better solution available for this problem.

share|improve this question

migrated from stackoverflow.com Oct 8 '13 at 6:54

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

You may want to look into Publishing Stored Procedure Execution in Transactional Replication which can provide significantly better performance for large batch operations since only the procedure execution is replicated, bypassing the operation being sent to Subscribers as a large, multi-step transaction.

If you have anymore questions please let me know. I hope this helps.

share|improve this answer
add comment

Your Answer

 
discard

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