The concurrent snapshot for publication is not available because it has not been fully generated or the Log reader agent is not running to activate it.

Hi everyone.

Welcome to my first post.

In my environment, we still utilize replication.

What in the world is replication?

Replication is a way to copy data from your source to destination server continuously.

Imagine if I am adding records to a database on your source server throughout the day and I would like the data updated to the target server. That's what is possible with replication.

Here's a great article for further reading:
https://www.sqlshack.com/sql-server-replication-overview-of-components-and-topography/

Back to the issue at hand.

1.
After reinitializing replication on the QA server, I received this message under the replication monitor > Distributor To Subscriber History:

The concurrent snapshot for publication is not available because it has not been fully generated or the Log reader agent is not running to activate it.

This was the other error message I was receiving under the replication monitor > Publisher To Distributor History:

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.

While still in SSMS, I went to check on my replication and noticed my subscription was missing:

But It was still missing even after running my script below to generate a publication and new subscription:

USE [replication database name]
GO
EXEC [dbo].[usp_ReplicationCreatePublication_publicationname]
WAITFOR DELAY '00:00:30';  -- Wait for snapshot agent job to finish before continuing
EXEC  [dbo].[usp_ReplicationCreateSubscription_subscriptionname] 'server','publication database'

2.
It turns out the stored procedures used in creating the publication and subscription were missing from my replication database. I went back to the Production version of my database and copied the script to recreate both stored procedures. You can do that by right clicking your stored procedure > 'Script Stored procedure as' > 'create to' > 'new query editor window' . Then you will be able to copy your stored procedure scripts from Production to QA.

Note: A word of caution... check the script to make sure it is not referencing specifically the Production server so there are no conflicts on your QA server.

3.
After I executed my 2 scripts to recreate the publication and subscription stored procedures on the QA server, I found these 2 stored procedure were dependent on 2 other stored procedures:

error#1
The module 'usp_ReplicationCreatePublication_publicationname' depends on the missing object 'dbo.usp_ReplicationChangeJobOwner'. The module will still be created; however, it cannot run successfully until the object exists.

error#2
Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'dbo.usp_Replication_Drop'.

So I also copied those stored procedures from Production to my QA server.

Now I have to test and see if adding back those 4 stored procedures fixed my issue by running the script above in step 1 again to generate a publication and new subscription.

Sigh... this still did not solve the issue.

When I launch the replication monitor in QA  I still receive this message under the replication monitor > Distributor To Subscriber History:

The concurrent snapshot for publication is not available because it has not been fully generated or the Log reader agent is not running to activate it.

4.
The best thing to do in this scenario is restore the latest copy of your replication database from Production to QA and add back permissions and replication (with publication and subscription).

Note: If your replication database is part of an Availability group or mirroring, you will have to remove it from the availability group first before you can restore the database to QA.

If you have any further questions, please post below.

Thank you and Happy reading,

-marshé hutchinson
#learnSQLwithME