Replication Issues

Posted on 2006-05-02
Last Modified: 2008-01-16

I had push transactional replication (with immediate updating subscribers) running without any problems for a few weeks. All of a sudden, with no apparent reason, synchronization between publisher and subscriber stopped working. This is the second time this has happened during the course of my testing.

Subscriber's status shows "The initial snapshot for for publication 'xxx' is not yet available.

This is a strange situation because:

1) An initial snapshot had already been generated which was used to initialize the subscriber
2) Synchronization was working well and now it seems that (with the status message above) the subscriber is waiting to be (re)initialized?
3) Transactions performed on either the publisher or the subscriber are successfully delivered to the distribution database, but the transactions are never delivered by the distribution agent to the intended recipients (publisher or subscriber)

I need to know why this is happening and how I can avoid this behavior in the future.

Extra 500 points will be given to anyone who can nail this issue.

Thank you.
Question by:xnmb
    LVL 4

    Expert Comment

    Dear xnmb,

    To sort out this problem, You need to drop/recreate the subscribtion with the "subscriber already has schema and data" option.

    stop the distribution agent, right click on it, select agent properties, and make sure sa is the job owner is sa. Right click again and select the verbose history agent. Restart your agent, and see if this helps.

    If not, reinitialize this subscription and try again.

    Chetan Sachdeva
    LVL 5

    Author Comment

    Thanks for your reply, Chetan.

    However, i've already looked into the distribution agent's output log (which was set to maximum verbose level) and did not find anything apparent that could have caused the problem.
    LVL 4

    Accepted Solution

    Dear xnmb,

    Basically the error which you explained in your query can be raised if the Distribution Agent is started before the Snapshot Agent has finished generating the snapshot. This error is raised only if the publication contains a single article. If the publication contains more than one article,

    If the Snapshot Agent for the publication has not been started since the subscription was created, or if it has not been started since the last time you chose to reinitialize the subscription, start the Snapshot Agent and let it complete before starting the Distribution Agent.

    If the Snapshot Agent does not complete, check the Snapshot Agent history for errors and address them.

    If the error continues to occur, increase the logging of the agent and specify an output file for the log. Depending on the context of the error, this could provide the steps leading up to the error and/or additional error messages.

    If still you are facing this error and it is not affecting your replication then use skiperrors parameter with your replication.

    Hope that will sort out your problem..

    Chetan Sachdeva
    LVL 5

    Author Comment

    thanks, but you have to keep in mind that when the problem (i really don't consider it an error) arose:

    1) a snapshot has already been generated (and completed)
    2) the same snapshot has already been applied (fully/completely) to the subscriber
    3) the setup was already running. replicated transactions were already being delivered both subscriber and publisher (updatable subscription)

    please help. i need a messiah.
    LVL 5

    Author Comment

    I think the "issue" was a result of the subscription being deactivated based on the distribution retention period.

    Since no one else posted any comments, I'm awarding you all the points.

    Thanks for your time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now