6

We are using Transactional Replications with updatable subscriptions. 1 publisher, 1 subscriber.

SQL Replication started encountering a constraint error on 3/6 at 10AM because people at two different offices entered data that violated a unique constraint. Totally expected.

However, at 12:08PM on 3/6, SQL Replication stopped reporting that error and instead said:

"The initial snapshot for publication 'TheDB' is not yet available."

Why would it stop retrying replication and instead start throwing this error? Now we need to regenerate the snapshot.

  • No DB backups or restores were performed at this time
  • The snapshot was successfully applied at the subscriber months ago when replication was initially set up.
  • There is a message in the log from 12:07 on 3/6 saying

[000] Request to run job COTOPSVR\TheDB-TheDB-COTCPSVR\DB-3 (from User CTOB\Administrator) refused because the job is already running from a request by User sa

  • No one manually initiated any job or did anything at all at 12:06PM.

Any ideas? I know how to fix this...I want to prevent this from happening again.

|improve this question
  • Check your agent jobs and see if they're running. Right-click on the publication in SSMS and check Snapshot Agent status. What do they say? – swasheck Mar 7 '13 at 17:22
  • They are all Running....but Distributor to Subscriber history reports that error. – Jeff Mar 7 '13 at 17:26
  • Are Pub, Dist, and Sub all on separate instances? – swasheck Mar 7 '13 at 17:27
  • Pub/Dist on same instance. Subscriber on a remote server. – Jeff Mar 7 '13 at 17:28
  • 1
    As an aside. In my experience, I've noticed that any issue the breaks schema will eventually break replication. – swasheck Mar 7 '13 at 18:26
4

With basic Transactional Replication there are a variety of agents.

Helpful MSDN article

There is the Snapshot Agent which is responsible for the initial snapshot of data and schema. This can be scheduled to run at regular intervals (I had mine scheduled for every 12 hours).

There is the Log Reader Agent, which reads the transaction logs and sends those which are marked for replication on to the distributor.

Finally, there is the Distribution Agent which is responsible for moving the snapshot(s) and the transactions from the distributor to the subscriber(s).

Your error is telling you that it cannot apply transactions that are marked for replication to the subscriber because the initial snapshot (and/or any of the subsequent snapshots) have not yet been applied. You need to troubleshoot why this snapshot is not being applied. There are many reasons why it could be happening.

Has the Distribution Agent lost permissions to the directory that contains the snapshot files?

Is there some blocking activity on the subscriber that is causing the snapshot to be applied slowly?

Have you overloaded the distributor and/or subscriber by pushing all articles from a large published database to all subscribers at the same time?

|improve this answer
1

I would suspect that your subscription was stuck with that error and not actually replicating any new transactions. At which point the subscription fell behind past the transaction retention period defined for that publication. The subscription will then recognize that replicated transactions it needs are no longer available as they have been purged from the distribution database.

When this occurs it will indicate the subscription needs to be reinitialized with a snapshot.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.