none
open transactions and log reader agent. RRS feed

  • Question

  • there are 2 tables (t1,t2) both are configured for transactional repl. A transaction (begin tran) was started at 10am which updates table "t1" which gets over in 10 minutes but the commit was done at 6pm. but in between 10pm and 6pm there were 100's of DML commands were done on table "t2" which are replicated to the subscriber. the transaction log is at 6pm is around 100gb. the question is.. when I commit the transaction at 6pm will the log reader agent traverse through the entire log  right from the start i.e. 10am all the way to 6pm? Will there be a performance impact or latency issues?


    Tuesday, February 13, 2018 9:48 AM

All replies

  • The Logreader need to read entire log (does not matter if the transaction is replicated or not). In your case it will need to read entire 100GB transaction log. There will be latency as it need to read the log, find all commands that are for replication and then insert into distribution db. You can play around with REAdBatchSize, ScanBatchsize for Log reader agent profile.
    • Proposed as answer by Hannah Yu Friday, February 16, 2018 8:58 AM
    Tuesday, February 13, 2018 12:01 PM
  • Hi rameshpothikara.

    >> when I commit the transaction at 6pm will the log reader agent traverse through the entire log  right from the start i.e. 10am all the way to 6pm? Will there be a performance impact or latency issues?

    Based on my understanding, the log reader agent will scan the entire transaction log on the publisher and detects the changes made to the data after the snapshot has been taken and records them in the distribution database. This might cause log reader agent latency, but you can refer to this blog to monitor it. 

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Hannah Yu Wednesday, February 14, 2018 5:35 AM
    • Proposed as answer by Hannah Yu Friday, February 16, 2018 8:58 AM
    Wednesday, February 14, 2018 5:34 AM