Problems with replication - concurrent snapshot not available

[davejam]

Hi all,

I have just moved some databases from our old faithful to the brand new all singin all dancing dedicated SQL server, but this is now causing me real issue with replication for our main database.

nb. both servers are running sql server 2005 on windows 2003

I moved several databases over at the same time and I can replicate for all other databases without a problem, these did not originally have replication so have no history with replication in them.

Our main database, allows me to create a publication "Main_Rep" and seems to create fine

Snapshot Agent Status
[100%] A snapshot of 1 article(s) was generated.

selected Generate snapshot from monitor (to force a new snapshot)
Log reader Agent Status
3 transaction(s) with 4 command(s) were delivered.

Created new subscription, filled in all details and completed with no faults.

When I look at the subscriptions View subscription status I have the following message.

The concurrent snapshot for publication Main_Rep' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the pu

When i log onto the server expecting to receive the publication there is no record of the publisher on the server, all login details are correct.

I have tried the same process with another of the databases that moved, this database works and details are on the external server and tables are populated.

Could my main db(that previously had replication set) still be harbouring problems due to this old replication, can I remove all replication for a DB specifically... as it appears everything works from the server for replication, just not this specific DB.

Thankyou for any help, tips or advice.




daveJam

it works on my machine, so technically i win!

 

[mrdenny]

You have to run the snapshot agent after you setup the subscription, not before.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[davejam]

found out how to fix.

It was an issue that shouldn't come about, when it copied the db from backup I had already created a publisher and subscriber for testing, when this copied over it had not removed the data in system tables - distribution - MSsubscriptions, meaining when i was creating a new one it would not remove the replicated records, meaing the subscription was looking for a database that was no longer there.

Removed all subscriptions and publications, deleted the records that were relevant to the database name in mssubcriptions and setup from scratch again.

Worked

daveJam

it works on my machine, so technically i win!