Distributor to subscriber history "No replicated transactions are available"

I am running transactional replication om SQL 2005 SP4 x64 Std edition.
There are many publishers which has one subscriber, my distributor db is at publisher server.
All publications are out of sync as Distributor to subscriber history "No replicated transactions are available" where as I can see new data in table also new command are delivering to distributor as I can see in first tab publisher to distributor history. The strange part is there is no error or security or space issue but distributor is not working and saying No replicated transactions even undistributed command shows 0. If I re-initialize and run snapshot it goes to status = 3 sometimes too and I have to update status to 2 but even after that sync is not working. Please help it`ll be very big issue if I had to rebuild everything. I am stuck !
LVL 1
EpurchaseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
DO you have the agents scheduled for sync-ing? Check all your replication agents and see what their history is.
EpurchaseAuthor Commented:
yes they are scheduled and are running some of them runs as soon as sql starts and some are scheduled in a few minutes Thanks
ZberteocCommented:
Do you have many publishers or many publications?

A publisher is a server, which can have multiple publications. Normally the publications on a publisher will have distinct set of articles(tables) just to "break" the replication into smaller, related and easier to manage subsets. Is that the case in your configuration?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

EpurchaseAuthor Commented:
That's typo..I just wrote it all quickly to get a quick help thanks for correcting.
 Yes 1 publisher and many publications, yes same case.
ZberteocCommented:
I would say to rebuild each publication and follow each step correctly and make sure on the subscriber you don't keep the FKs. They are not needed if that side if only for reading anyway because the relation integrity is controlled on the publisher. Here is an article with the details of how to setup the replication:

http://www.techrepublic.com/blog/how-do-i/how-do-i-configure-transactional-replication-between-two-sql-server-2005-systems/

also here

http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step
EpurchaseAuthor Commented:
yes agree but this was running fine till last Saturday since 2009 :(
ZberteocCommented:
cood be a network glitch. You will have to push a new snapshot for each publication.
EpurchaseAuthor Commented:
I tired that for example, I created snapshot for one publication and it created fine on a share at publisher server, dist agent was still saying no snapshot available so its checked its status was 3 (it wasn't interrupted, it is concurrent) so updated it to 2 but it again says no transaction replication available instead of applying snapshots to subscriber. Thanks
ZberteocCommented:
Remove the publication and build it again.
EpurchaseAuthor Commented:
I have 33 publications :(
ZberteocCommented:
You must be doing something wrong or missing a step. It is difficult to pinpoint the problem without seeing what exactly you are doing. It could be some orphan SQL agents... Make sure you have only the SQL agents that you need, and the latest for each publication. Remove older ones.

Try to fix the smallest publication, do not remove it yet, and maybe you will figure it out. If you can't than you will have to do it manually, remove and add back.
EpurchaseAuthor Commented:
i tired running dist agent with -Outputverboselevel [4] option it says
call sp_MShelp_repl_agent ............
The subscription to publication 'Publication' has expired or does not exist.

So do i need to delete subscription and recreate/re-init it ? should work.
ZberteocCommented:
Did you check if you have orphan agents? Maybe you ran that one.
ZberteocCommented:
I remember i had this issue when re initializing a publication it would create new agents but not removing the original ones and then wouldn't work. The new agents have the same name only the suffix number in the name is higher. The highest number would be the last agent and the correct one. I haven't dealt with replications in some time so I might be wrong here but is worth checking...
EpurchaseAuthor Commented:
I have only three jobs for the one that says expired
1) Repl-logreaderAgent (shared)
2) Repl-DistAgent
3) Repl-SnapshotAgent

Still says expire... any Tsql way to check or update? i have gone ahead and deleted sub of one publication to see if after re-init it works or not. Thanks a bunch for prompt replys and help.
ZberteocCommented:
What do you have in the Replication Monitor?
EpurchaseAuthor Commented:
After re-creating subscription and generating new snapshot Replication Monitor still says no snapshot available and it is in status 3 in mssubscriptions table. output still says
2015-10-28 12:05:35.379 The subscription to publication 'NAME' has expired or does not exist.
I wonder why it is saying 12:05 however server time on both servers is same 7:05 AM
ZberteocCommented:
5hr ahead. Isn't that UTC?
EpurchaseAuthor Commented:
Time zone is CST(CDT)
ZberteocCommented:
Then why 2 different times?
EpurchaseAuthor Commented:
I guess these kind of logs shows in UTC by default. your help is highly appreciated please keep me doing something :)
ZberteocCommented:
Not really. The times should be the ones from the servers OS, publisher, distributor and subscriber.

One question. You said the replication was working. What happened that broke it? Did make any change to the system, server? An upgrade maybe?
EpurchaseAuthor Commented:
I checked it all , there is no patches or changes but before it stops working there disk space issue where distribution database log resides but the space was reclaimed at that time , now there is now space issue.
EpurchaseAuthor Commented:
*No space issue, space was reclaimed by other stuff no one touched distribution database thanks
ZberteocCommented:
EpurchaseAuthor Commented:
The cleanup jobs are disabled for now, retention periods verified for agent/sub. Upon Re-init

The concurrent snapshot for publication 'NAME' 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....... it got completed as per snapshot agent.
EpurchaseAuthor Commented:
Also "use the current snapshot" is always grayed out, why I cant use the snapshot I just created sometime back? is it invalid somehow. The account is admin and location of snapshot is standard with all permissions are good for admin.
ZberteocCommented:
The only solution I see is to remove each publication and rebuild it. It should not be such a big deal even if you have 33 of them. One think that you can do is to script out each publication before you remove it, at least you will not have to build it manually.

How to script publication objects:

https://technet.microsoft.com/en-us/library/ms151213(v=sql.105).aspx


Before rebuild make sure there are no traces of that publication left anywhere, not on publisher, not on distributor and not on subscriber. You can check using:

select * from msdb..MSdistpublishers
select * from distribution..MSpublisher_databases
select * from distribution..MSpublications
select * from distribution..MSarticles
select * from distribution..MSsubscriptions

delete from distribution..MSarticles where publisher_db = '<NameOfDatabase>'
delete from distribution..MSsubscriptions where publisher_db = '<NameOfDatabase>'
EpurchaseAuthor Commented:
I have cleaned up distribution manually and recreated subscription as pull to load balance dist agents, it actually working now so I am checking for other publications now. Thanks

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EpurchaseAuthor Commented:
This is resolved by above ... Thanks for all the help though :)
EpurchaseAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Epurchase's comment #a41136224

for the following reason:

This way we actually able to resolve the issue and even load balance the replication traffic.
ZberteocCommented:
I don't think is fair to close this without granting some points. We spent time here and directed you to this solution.
EpurchaseAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Epurchase's comment #a41136224
Assisted answer: 100 points for Zberteoc's comment #a41129904
Assisted answer: 200 points for Zberteoc's comment #a41135010
Assisted answer: 200 points for Zberteoc's comment #a41135173

for the following reason:

Zberteoc helped me to achieve the solution :)
ZberteocCommented:
@Epurchase,

If you award points to any answer you got then you don't have to close the question, just awards the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.