Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Replication Issues

Posted on 2006-05-02
5
Medium Priority
?
409 Views
Last Modified: 2008-01-16
SQL2K5 SP1/WIN2K3

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.
0
Comment
Question by:xnmb
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:csachdeva
ID: 16594282
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.

Regards,
Chetan Sachdeva
0
 
LVL 5

Author Comment

by:xnmb
ID: 16595105
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.
0
 
LVL 4

Accepted Solution

by:
csachdeva earned 1000 total points
ID: 16595367
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..

Regards,
Chetan Sachdeva
0
 
LVL 5

Author Comment

by:xnmb
ID: 16595958
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.
 
0
 
LVL 5

Author Comment

by:xnmb
ID: 16611931
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

571 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