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?
VijayAsked:
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.
0
 
VijayAuthor Commented:
Thank you Very much Raja.
0
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.