Replication - This is one of the Interview Question

There is an update statement that affects 1 Million rows on the publisher which generates 1 Million records in transaction log. Due to this large transaction we are getting Log Reader Agent timeouts. How to handle these kinds of large transactions when replication configured?
Who is Participating?
Raja Jegan RSQL Server DBA & ArchitectCommented:
I've encountered this scenario once, one developer ran a single update statement to update 22 Million records in a single shot.
We had issues with Log Reader agent and then devised the below approach which works without any issues.

We can handle it by updating 1 Million rows in batches of 1000/5000 so that the records get picked up by Log Reader agent to Distribution agent and then subsequently to Subscriber database as well without any issues.
VijayAuthor Commented:
Thank you Very much Raja.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.