SQL Server 2012 Replication - MSrepl_commands growing all set to in progress

Question

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 repl_commands table

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.

General things:

• All replication is transactional without any snapshots
• immediate_sync is 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?

Answer 1

So we're working now, I'm not 100% sure which part of this fixed the issue but I'm putting it down for if anyone else has this issue in the future where there doesn't seem a logical reason for the distribution growth.

Our distributor is on a windows failover cluster across two machines along with a Reporting Instance (and a MSDTC).

• The issue occurred after a restart where the distributor moved from server 2 to server 1 running along side reporting(and MSDTC).

• After server 2 restart was complete Reporting was moved over to Server 2.

• This is when the issue was noticed.

• The Distributor was moved over to Server 2 (Alongside reporting) but this seemed to have no beneficial effect

• The Distributor was moved back to Server 1 and MSDTC was moved to server 2 with Reporting

  At this point running the sp_MSdistribution_cleanup with a max time of just shy of how long the server had been running for (by 6 hours) and it cleared out everything (Took close to 3 hours to complete but it got there)

Answer 2

When you have issues with transactional replication, you have to determine if the issue is with the logreader or with the distribution agent. You can determine this by running a tracer token. If the issue is with the logreader agent, you have to determine if it is the reader or the writer (is the problem with reading the transaction log, or is the problem writing to the distribution database). You can determine this from querying mslogreader_history.

Reader fetch = Time reading the transaction log. Wait = Time spent waiting on writer

Writer write = Time spent writing into the distribution database. Wait = Time spent waiting on reader

If there is a high wait time on Reader fetch then the Writer write is slow. We need to make sure the clean up job is working properly and it isn't causing blocking.

If there is a high wait time on Writer write then Reader fetch is slow. Look at the size of the transaction log and run DBCC Loginfo to see if we have excessive VLFs.

If the tracer token indicates the problem is with the distribution agent, then we have to determine if the problem is reading from the distribution database, or writing to the subscriber. You can determine this by querying msdistribution_history.

Reader fetch = Time reading the distribution database. Wait = Time spent waiting on writer

Writer write = Time spent writing to the subscriber. Wait = Time spent waiting on reader

A high wait time on Reader means the Writer is slow writing to the subscriber. Check for resource contention on the subscriber.

A high wait time on Writer means the Reader is slow reading from the distribution database. Make sure cleanup job is running and not causing blocking.