none
Log Reader Agent

    Frage

  •   Hi, We have few servers where updates\deletes are made in big batches and often we come across performance problems in replication. While we try to keep those batch size small, sometimes they go out of control. I came across this option "

    MaxCmdsInTran" 

    for log reader agent and it says "This parameter specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database." There is also a warning saying "this was not designed to be always turned on ". What does it mean? What are the drawbacks if we have that turned on? any suggestions?

    Thank you!

    Montag, 2. Juli 2018 18:34

Antworten

  • Yes, if your transaction aborts or is rolled back midway there is a possibility that these transactions will have been picked up by the log reader agent and are already in the subscriber or on the way to the subscriber. You will need to be able to identify them and remove them from the subscriber(s).

    This may be a non-trivial task. Some of my clients have decided to use this anyway and have not had a bad experience yet as these transaction rollbacks do not happen for them.

    • Als Antwort markiert SQLmaddy Dienstag, 3. Juli 2018 15:53
    Dienstag, 3. Juli 2018 13:23
    Moderator
  • Here are a few suggestions - do your batch process in a stored procedure and then replicate the execution of the stored procedure. Note that all dependencies must be in place on the subscribers for this to work.

    Also think about removing the article (tables affected) from the publication, do your batch update, add the articles back in.

    In many cases you will find that the snapshot and delivery option can be faster than the batch update.

    • Als Antwort markiert SQLmaddy Dienstag, 3. Juli 2018 15:53
    Dienstag, 3. Juli 2018 14:08
    Moderator

Alle Antworten

  • What it means is that if you update 100,000 rows in a single command and maxcmdsintran is set to 10,000, the 100,000 commands will be broken up into 10 groups of 10,000.

    You would need to stop the log reader agent to make this change and do it at a time where there is no activity or little activity on the publisher.

    If your update fails, the transactions in flight will already be on the subscriber but rolled back on the publisher.

    Montag, 2. Juli 2018 19:11
    Moderator
  •  But only committed transactions will be replicated if i understand it right? Isn't the case after the transaction is committed, log reader agent will then break that tranx into small chunks? If its the other way where the transaction will be replicated even before it is committed ( i ve to read internals if its possible?) , then we shouldn't use this unless its 1 time event ?

    Thank you very much!

    Montag, 2. Juli 2018 19:24
  • No, uncommitted will be replicated as well.

    The log reader agent will never break large transactions into chunks unless you set maxcmdsintran.

    Be careful about using this. If you do this mid transaction you can break the log reader agent and you will need to reinitialize.

    Montag, 2. Juli 2018 19:44
    Moderator
  • What exactly are the "performance problems" in replication? A general best practice is to perform deletes in smaller chunks on the database and if you follow that, you shouldn't have to tweak the log reader agent settings and default would work fine. As Hilary pointed, you run the risk of having to reinitialize it if it's not executed correctly. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Montag, 2. Juli 2018 19:57
  • ah, uncommitted will be replicated? That makes sense to not implement this. Original plan was to roll this out across all the servers where we have huge transactions permanently, sounds like not a good idea.

    Thank you!

    Dienstag, 3. Juli 2018 12:40
  • Yeah, I m aware of that smaller batches and better performance. long story short : We have few edge cases where we can't control batch size (i know that sucks!) , so trying to find workarounds :)
    Dienstag, 3. Juli 2018 12:41
  • Yes, if your transaction aborts or is rolled back midway there is a possibility that these transactions will have been picked up by the log reader agent and are already in the subscriber or on the way to the subscriber. You will need to be able to identify them and remove them from the subscriber(s).

    This may be a non-trivial task. Some of my clients have decided to use this anyway and have not had a bad experience yet as these transaction rollbacks do not happen for them.

    • Als Antwort markiert SQLmaddy Dienstag, 3. Juli 2018 15:53
    Dienstag, 3. Juli 2018 13:23
    Moderator
  • Here are a few suggestions - do your batch process in a stored procedure and then replicate the execution of the stored procedure. Note that all dependencies must be in place on the subscribers for this to work.

    Also think about removing the article (tables affected) from the publication, do your batch update, add the articles back in.

    In many cases you will find that the snapshot and delivery option can be faster than the batch update.

    • Als Antwort markiert SQLmaddy Dienstag, 3. Juli 2018 15:53
    Dienstag, 3. Juli 2018 14:08
    Moderator
  •   Great idea on stored proc execution method, It may or may not work for us if we expect these big operations from adhoc queries. I ll have to explore our environment a bit more to find the possibilities of large batch operations being rolled back. Its pretty common for small transactions to rollback but i don't think it applies in that scenario. 

     Thank you for sharing all the options as always @hilary! Appreciate your help!

    Dienstag, 3. Juli 2018 15:53
  • No, uncommitted will be replicated as well.

     I m trying to produce this behavior in test environment and couldn't do it. i updated the log reader agent and tried to run a big batch on this tblTest where it has 100000 rows:

    begin tran
    go
    update tblTest
    set col1 = 10000
     Log reader agent didn't replicate anything until I committed the transaction. Am I missing anything?

     

     

    Donnerstag, 5. Juli 2018 01:45