I've got an odd problem here. I was trialling the use of 'initialize from lsn' in our test replication setup and now have broken all publications from one of our databases, rebuilding from scratch (applying a new snapshot) has not fixed the issue.
In Replication monitor, distributor agent running continuously and reporting:
No replicated transactions are available
Tracer token is reporting that:
- Publisher to Distributor: 00:00:02
- Distributor to Subscriber: Pending...
- Total Latency: Pending...
When I investigated the distribution logs using:
SELECT time, CAST(comments AS XML) AS comments, runstatus, duration, xact_seqno, delivered_transactions, delivered_commands, average_commands, delivery_time, delivery_rate, delivery_latency / (1000 * 60) AS delivery_latency_Min, agent_id FROM dbo.MSlogreader_history WITH (NOLOCK) WHERE agent_id = 5 ORDER BY time DESC; SELECT * FROM dbo.MSdistribution_history WHERE agent_id = 125 ORDER BY time DESC;
I can see that the latest xact_seqno from:
- LogReader agent is: 0x00000378000384880003
- Distribution agent is: 0x000CA68B000010A8000A000000070000
As a comparison on one of the other databases (going to the same subscriber), I get:
- LogReader agent is: 0x00000D140001F52A0003
- Distribution agent is: 0x00000D140001F52A0003000000000000
To me it looks like the distribution agent is remembering the old broken xact_seqno (from when I made a mistake when testing 'initialize from lsn'), but now it appears to have permanently broken all replication from that database.
- PubA - One publisher hosting several databases that are replicated
- Dist - A separate distributor
- SubA - First subscriber
- SubB - Second subscriber
- PubA.DB1 = Publisher DB that is working fine
- PubA.DB2 = Publisher DB that is broken
- SubA.PullWorking1 = 1st subscriber has a working pull subscription from DB1
- SubA.PullBroken2 = 1st subscriber also has a broken pull subscription from DB2
- SubA.PushWorking1 = 2nd subscriber has a working push subscription from DB1
- SubA.PushBroken2 = 2nd subscriber also has a broken push subscription from DB2