locked
Replication Latancy RRS feed

  • Question

  • Hi All,

    I am using transaction replication In my Environment. 
    Question: At the time when batches completed from Application team, daily it will take  5  to 10min to apply all commands to replicated database, but from last 2-3 days it is taking  1 to 1 and half hour.
    Can anybody suggest what might be the reason?
     
    Environment on both servers prod as well as replicated
    __________________________<o:p></o:p>

    SQL Server 2008 r2 Standard
    Windows server 2008r2 enterprise
    CPU 64 (Hyper threading Enable)
    64 GB Memory<o:p></o:p>

     

    Thursday, July 17, 2014 6:12 AM

Answers

All replies

  • Hi Imran,

    Read this post http://blogs.msdn.com/b/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx. It clearly explains the different reasons you could have a latency issue.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Prashanth Jayaram Thursday, July 17, 2014 1:19 PM
    • Marked as answer by ImranKazi Thursday, July 24, 2014 3:22 AM
    Thursday, July 17, 2014 9:40 AM
  • Hi ImranKazi,

    I agree with Ashwin, I've found that link very useful for troubleshooting performance issues in Transactional Replication and finding the bottleneck.

    Note that if you have a large batch of transactions to replicate daily then you may need to replicate during non-peak times or replicate as stored procedure executions which can provide significantly better performance.  Details on this approach are covered in Publishing Stored Procedure Execution in Transactional Replication.


    Brandon Williams (blog | linkedin)

    Thursday, July 17, 2014 5:02 PM
  • Hi Imran,

    Read this post http://blogs.msdn.com/b/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx. It clearly explains the different reasons you could have a latency issue.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Hi Ashwin,
    I am getting following error when i am executing 

    sp_replcmds @maxtrans = 1
    sp_replshowcmds @maxtrans = 1

    Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.

    Friday, July 18, 2014 3:51 AM
  • Hi ImranKazi,

    I agree with Ashwin, I've found that link very useful for troubleshooting performance issues in Transactional Replication and finding the bottleneck.

    Note that if you have a large batch of transactions to replicate daily then you may need to replicate during non-peak times or replicate as stored procedure executions which can provide significantly better performance.  Details on this approach are covered in Publishing Stored Procedure Execution in Transactional Replication.


    Brandon Williams (blog | linkedin)

         Actually there are some non-replicated article dependencies 
    Friday, July 18, 2014 4:00 AM
  • There should be only one connection running this command including your log reader. So stop it first before running these commands.

    Check the remarks section of this article - http://msdn.microsoft.com/en-us/library/ms186983.aspx

    sp_replcmds is used by the log reader process in transactional replication.

    Replication treats the first client that runs sp_replcmds within a given database as the log reader.

    http://msdn.microsoft.com/en-us/library/ms175114.aspx

    sp_replshowcmds is used in transactional replication.

    Using sp_replshowcmds, you can view transactions that currently are not distributed (those transactions remaining in the transaction log that have not been sent to the Distributor).

    Clients that run sp_replshowcmds and sp_replcmds within the same database receive error 18752.

    To avoid this error, the first client must disconnect or the role of the client as log reader must be released by executing sp_replflush. After all clients have disconnected from the log reader, sp_replshowcmds can be run successfully.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    • Edited by Ashwin Menon Friday, July 18, 2014 8:06 AM
    • Proposed as answer by Sofiya Li Tuesday, July 22, 2014 9:09 AM
    Friday, July 18, 2014 7:55 AM
  • As I observer Only 3 tables are taking long time to be replicated. These are Log tables which are more than 150GB in size. 
    I am thinking to apply new scenario over here as follow :
    All batches complete at 4:30AM and business people start from 6AM  
    so i need to sync all data between prod to replicated server before 6AM
    usually there will be only 10 min latency but twice in a month it took 3 hours (application team runs some more Batches at this time)
    So does snapshot replication solve my problem in this scenario if I run manually when above situation happened

    What will be the Pros and Cons
    or is there any better way 



     
    Tuesday, July 22, 2014 6:53 AM
  • Hi Imran,

    To be very frank, we wont be able to say whether snapshot replication is going to help you unless we know how much time it is going to take.

    I think first you should try to see if you can improve the replication performance by taking a look at the article which I mentioned earlier.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, July 22, 2014 11:38 AM