I'm stuck looking at this problem that we've had since our last restart almost a week ago.
The distribution database is growing and no longer pruning; it appears that everything in the
MSdistribution_history are all set to either 3 or 4 (in progress / idle) all the idle report as no replicated transactions are available)
We're now at 261K values in the history table and 195M rows in the
All replication through this distributor goes from one location to up to three different servers, there is currently a maximum of 4 seconds latency across all replication, cant see anything that isn't complete
Obviously running the
sp_MSmaximum_cleanup_seqno command returns nothing as there is nothing that is set to be success (status 2)
Does anyone know what might have gone wrong or any possible solutions.
- All replication is transactional without any snapshots
immediate_syncis turned off on all replication
- max retention is set to 72 hours
- no errors are being reported
- there were a few issues when the server came back up where all the jobs retried twice.
- The publisher server was patched from 2012_SP2_CU7 to SP3_CU2 and the distributor instance has not yet been patched
We're going to try a fail over to the secondary node tomorrow to see if that will clear it out
I can always manually delete stuff from the repl_commands but I'm highly hesitant to do this.
Any advice or ideas?