I have a transactional replication topology as follows:
NY Publication Database same server holds distributor
NY - Subscriber
LON - Subscriber
HK - Subscriber
We are using bog standard, one-way transactional replication. All machines SQL 2008 R2
The snapshot gets applied fine to all subscribers, and then starts distributing commands, as you would expect, but to the one subscriber only (London), a whole bunch 2000-4 million commands just get "stuck". You can see the commands in msrepl_commands, you can see the transactions in msrepl_transaction. Replication monitor shows that there are xxxxx undelivered commands. All commands are being delivered without issue to the other 2 subscribers.
The problem is that they just stay there. Replication monitor says that there is nothing to be replicated, and nothing happens, they just stay where they are. The only way I have found around it, it to tear down the subscription and re-initialize!?!
I am aware that there are network "blips" between NY and Lon, and I can see in msrepl_errors, that there are some communication failures. However, I would expect replication to pick them back up? The distribution agent is running fine.
How do I get around this issue, and force SQL Server to pick these commands up, and deliver them.