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
