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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 Oct 8 '13 at 6:54

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

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

Your Answer


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