Replication issue

Posted on 2012-08-17
Last Modified: 2012-08-20

I have configured transactional replication on my servers.
Log reader agent is moving transactions from publisher to distributor correctly.
but for some reason the transaction from distributor to subscriber is taking a long time,
Undistributed commands from distributor to subscriber is high
and the estimated time to apply the transaction to subscriber is also increasing.
How can i push this transactions to the subscriber.

Please advise.
Question by:isonali
    LVL 25

    Expert Comment

    Check the server status of the subscriber. I mean to say in terms of CPU, memory, blocking,etc. If the subscriber health is good then you can run a profiler on the replication stored procedure to find out what time it is taking and why with the help of the plan.
    LVL 26

    Accepted Solution

    Make sure that on the publisher you have all the indexes you need on the tables and in the replication properties you specify to replicate all the indexes from publisher, foreign keys are not necessaary, to subscriber. After that reinitialize your transaction, execute the snapshot agent. Also you can run n a

    EXEC sp_who2 active

    command on the subscriber and see if there are some ongoing processes that take long or if there is any blocking. You have to solve those.
    LVL 38

    Expert Comment

    by:Jim P.
    Also what version of SQL are you using, and what OS?

    Select @@Version

    Open in new window

    LVL 38

    Expert Comment

    by:Jim P.
    Can you give us an idea of the resolution?
    LVL 26

    Expert Comment

    That is not enough my friend. People here spent time to give you decent answers so just saying the issue is solved won't work. Maybe you can give some more details on this. After all what you owe are just some points not money.

    Author Comment


    Log reader agent showed some undistributed transactions which were not marked for
    I guess that was the reason the latency was increasing.
    I simply dropped the subscription and the target database,
    Rebuild all the indexes at the publisher side.
    Created a new publication and subscrber and and it seems to have resolved the issue.

    Thanks all for your help...


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now