SQL Server with Mr. Denny

Aug 12 2015   6:00PM GMT

The concurrent snapshot for publication ‘X’ 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

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:
Replication
Snapshot
SQL error messages
SQL replication
SQL Server
SQL Server replication
SQL Server transaction logs
SQL transaction logs
Transaction logs

Recently I was working on SQL Server Replication for a client, specifically removing and reading a table from replication to fix a different problem. After putting the table back and running the snapshot agent to create a differential snapshot I was greeted with the error message “The concurrent snapshot for publication ‘X’ 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”, and yes that’s all of the error that was shown.

Doing some Google searching gave me basically no useful information other than people had dropped the publication and recreated it, sometimes having to completely remove replication and put it back in order to make the error go away. Neither of those was a very good option as this publication has some very large tables on it, and the distributor has 4 servers with about 80 publications several of which have multi-billion row tables being replicated.

On a whim I decided to look at the log reader history to make sure that everything there was running smoothly there. It wasn’t. There I was getting the message “Approximately 123500000 log records have been scanned in pass # 3, 0 of which were marked for replication.” in the history. Now this message I’ve seen before. This normally means that there are WAY to many VLFs in the publication database. Last time I saw this there were over 100k VLFs. This database only has 754.

So fixing this became much easier now. Slow, but easy. Fixing this simply requires shrinking the transaction log then manually growing it again. Because replication is enabled on the database you can’t just shrink the transaction log. You have to wait for replication to read the transactions from the log, then backup the log, then shrink the file. And you have to do this over and over again because the replication log reader is running so painfully slow. Especially on a system with a heavy workload like this system has. Once I got all the transactions read by the log reader and I was able to get the transaction log shrunk down to three VLFs (basically as small as you can shrink the transaction log) the log reader was happy as was the distribution agent. I then manually grew the transaction log back to the correct size and everything was happy with the system again.

Basically the error message is a really bad one. It says the snapshot isn’t available, but it means that the transaction which tells the distribution that it can process the snapshot because all the transactions from before that have been processed by the log reader have been processed hasn’t been read yet. If you get this error run DBCC LOGINFO on your publisher and see what it kicks back. If there’s a lot of VLFs you have the answer to your problem. Odds are the threads on forums that I found all worked because removing and rebuilding replication takes a while so the log reader was able to get through the part of the log it needed to while they were rebuilding everything manually.

Denny

2  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • Manishkumar1980
    Great Indeed. Very Helpful
    20 pointsBadges:
    report
  • mct1960
    ...or it could just be that the transaction log is swamped and all you need to do is be patient.  We had this  when re-publishing an article during index optimisation (REBUILD) of some very large tables.  What with the log backup consequently taking an age and this message, it was a worrying time (if you know replication :) ) but al we had to do was wait, as there were no errors.  The moral being, DON'T PANIC!  Just wanted to share that in case it helps someone else's nerves.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: