The concurrent snapshot for publication 'xx' is not available because it has not been fully generated or the Log Reader A

Question

I am having all sorts of trouble getting SQL Server (2005) to work properly. When I run the client (SQL Server Express 2005) I get the following error

The concurrent snapshot for publication 'xx' is not available because it has not been fully generated or the Log Reader A

The error is cropped off.

I have tried:

• Deleting the subscription
• Deleting the publication and starting again
• Restarting the server and client.
• Shrinking the database

All without any change.

I know that the client can see the network share \\servername\repldata which has a UNC subfolder with the snapshot in it (a folders deeper with the pubName and dateTime encoding as expected)

Any tips or trips would be REALLY appreciated

Maidservant

Can you qualify what type of replication you are using, snapshot/transactional/merge? Are you using push or pull subscriptions? – Semolina

Answer 1

Here is the solution: http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/baf99181-2fe1-4e6b-ba45-5d1d08103550/

1. Delete the subscription & the whole publication that is not working

2. Run a select on these tables to see if there are still rows for the publication database available:

   select * from msdb..MSdistpublishers
   select * from distribution..MSpublisher_databases
   select * from distribution..MSpublications
   select * from distribution..MSarticles
   select * from distribution..MSsubscriptions
   

3. Now, delete all rows that have still the publisher_db from the just deleted publisher database:

   delete from distribution..MSarticles where publisher_db = '<NameOfDatabase>'
   delete from distribution..MSsubscriptions where publisher_db = '<NameOfDatabase>'
   

4. Re-create the publication & the subscriber as normal

That should do the trick :-)

Larimor

No need to apologize for a late answer -- such contributions are more than welcome! – Dosi

Link is broken. – Thaw

Answer 2

1. Has the snapshot for the publication been generated? Sometimes this takes a looong time and sometimes it gets deferred to a later time. If it is made, you should be able to find it on disk somewhere.

2. Make sure that the Log Reader is running on the Publisher, if this is Transactional Replication, which I assume it is.

Higgledypiggledy

Answer 3

In my situation, it was due to fact that "MSSubscriptions" table on distributor database had nosync_type=1 and status=2. Changed above to 0 and 1 respectively.

Reinitialized the snapshot again and then distributor agent started picking up these changes.

Organizer

Answer 4

I just reinitialized the snapshot again (new snapshot, don't reuse old one) and then distributor agent started picking up these changes.

Hindoo

Answer 5

For me it was just matter of giving the Log-Reader and Distribution service accounts DB_Owner access to the publishing database. I'm not sure if they were both required, but it was an easy fix.

Plastid