none
Replication not replicating fast enough (Replication Transaction Rate is low?) RRS feed

  • Question

  • Hello

    Im hoping someone can point me in the right direction.

    I currently have transactional replication model and for some strange reason the Repl. Pending Xacts keep on increasing. They are currently at 20 million. Having restarted the distributor I noticed that the Repl. Trans. Rate is currently 0

    Indeed, doesnt seem to be transferring any data. 

    I assume this is because of a network latency between my publisher and my distributor which sit on different server machines.

    Anyone help?

    K

    Tuesday, August 4, 2015 4:48 PM

Answers

  • It definetelly could be network latency, but transfer rate of 0 implies that there are other factors in play.

    We would need more information to be able to troubleshoot further:

    How large is the transaction log on your publisher database? If the transaction log is rather large, lets say over 100GB (this is an arbitrary number btw) replication would slow down since logreader agent would get behind.

    What is log_reuse_wait for the TLog? is it REPLICATON or ACTIVE_TRANSACTION, or is it something like LOG_BACKUP/NOTHING?  if there is a huge active tran that is currently being delivered to your distribution server from the publisher database all of the commands need to be delivered first before it would deliver the transaction to the subsribers. If the log size is rathe small and log_reuse_wait is nothing/Log_backup or something not affecting replication, we could probably eliminate Logreader agent as our problem here, of course if there is no errors on the agent itself.

    Are you agents: Logreader,Distribution running in Verbose mode to help troubleshoot?

    Are there any errors in replicaton agents that are preventing the transaction to propagate?




    Tuesday, August 4, 2015 6:45 PM
  • It could be because the log reader agent is still reading a large transaction from the log. What does the log reader agent status read?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, August 5, 2015 2:45 PM
    Moderator
  • Hi Kashif

    Can you post output of mslogreader_history table using below query

    if output of comments  (xml) column is state2 or 3 then you have issues

    State 2 means writing replicated transaction at distributor is issue

    State 3 means reading replicated transaction from publisher log is issue

    => Replication

    USE distribution 
    GO 
    SELECT time, 
    CAST(comments AS XML) AS comments, 
    runstatus, 
    duration, 
    xact_seqno, 
    delivered_transactions, 
    delivered_commands, 
    average_commands, 
    delivery_time, 
    delivery_rate, 
    delivery_latency / ( 1000 * 60 ) AS delivery_latency_Min 
    FROM mslogreader_history WITH (nolock) 
    WHERE time > getdate()-1 
    ORDER BY time DESC 

    Please also verify if you have enough space in distributor server


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem



    Thursday, August 6, 2015 3:54 AM

All replies

  • It definetelly could be network latency, but transfer rate of 0 implies that there are other factors in play.

    We would need more information to be able to troubleshoot further:

    How large is the transaction log on your publisher database? If the transaction log is rather large, lets say over 100GB (this is an arbitrary number btw) replication would slow down since logreader agent would get behind.

    What is log_reuse_wait for the TLog? is it REPLICATON or ACTIVE_TRANSACTION, or is it something like LOG_BACKUP/NOTHING?  if there is a huge active tran that is currently being delivered to your distribution server from the publisher database all of the commands need to be delivered first before it would deliver the transaction to the subsribers. If the log size is rathe small and log_reuse_wait is nothing/Log_backup or something not affecting replication, we could probably eliminate Logreader agent as our problem here, of course if there is no errors on the agent itself.

    Are you agents: Logreader,Distribution running in Verbose mode to help troubleshoot?

    Are there any errors in replicaton agents that are preventing the transaction to propagate?




    Tuesday, August 4, 2015 6:45 PM
  • It could be because the log reader agent is still reading a large transaction from the log. What does the log reader agent status read?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, August 5, 2015 2:45 PM
    Moderator
  • Hi Kashif

    Can you post output of mslogreader_history table using below query

    if output of comments  (xml) column is state2 or 3 then you have issues

    State 2 means writing replicated transaction at distributor is issue

    State 3 means reading replicated transaction from publisher log is issue

    => Replication

    USE distribution 
    GO 
    SELECT time, 
    CAST(comments AS XML) AS comments, 
    runstatus, 
    duration, 
    xact_seqno, 
    delivered_transactions, 
    delivered_commands, 
    average_commands, 
    delivery_time, 
    delivery_rate, 
    delivery_latency / ( 1000 * 60 ) AS delivery_latency_Min 
    FROM mslogreader_history WITH (nolock) 
    WHERE time > getdate()-1 
    ORDER BY time DESC 

    Please also verify if you have enough space in distributor server


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem



    Thursday, August 6, 2015 3:54 AM