SQL Server Replication: Subscriber Is Not Visible In Subscriber List

Some time back i got chance to work on env. having replication setup between OLTP and reporting servers pending for migration.

After Migrating SQL Server Production Server we faced series of issues, One of them was at reporting server  we are not able to see any subscriber in subscriber list where as at publisher server subscribers were visible. So we have check couple of things here

1. Replication was Transaction replication with pull subscription. And replication jobs are failing with below error.

To verify if we have some pending transactions i used tracer token and found transactions are not moving from distributor.

2. Result of sp_replcmds at publisher says :

“Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedurewas executed or executesp_replflush overthat connection before starting the LogReader Agent or executing another log-related procedure.”

3. Going no where in replication monitor we check Distributor to subscriber history and error says 

"Shared Memory Provider: No process on the other end of pipe"

This says about network error but all protocols was enable on both publisher and subscriber. This means issue is with databases.

4. Son now we try to reinitialize subscriber from publisher but again no luck and it failed with below error

"Subscription status of the object could not be changed. Could not update the distribution database subscription table."

5. So next option is try to create subscriber on reporting server even though subscription does exist on publisher.

Ah again its fails with error:

"Cannot create the subscription because subscription already exists "

    
This clearly says there is something wrong on subscriber server or subscriber databases. After doing some check i realize some one have restore subscription database during migration.

So culprit was found and we drop subscriber from publication databases and recreate them.

Result is positive and replication again starts working!!