msrepl_transactions and msrepl_commands table cleanup


  • Hello replication experts,

    Can someone please explain, How do I check in msrepl_transactions and msrepl_commands tables to see if my transaction/command got successfully replicated or not to subscriber? 

    The reason to ask this question is, I have a msrepl_transactions table with 276980560 records and the distribution clean-up is not removing those records for some reason. The issue is only with msrepl_transactions table. The clean-up for msrepl_commands is working fine. It has only 23000 records. So trying to understand why it is behaving like this. My SQL Server version on publisher, distributor and subscriber is Enterprise Edition(12.0.4100.1).

    How do I troubleshoot this? Any help would greatly be appreciated.

    Wednesday, June 27, 2018 2:00 PM

All replies

  • Best thing to do is to use tracer tokens to see how far your log reader agent and distribution agent are behind.

    If the agents are behind your transaction might be inflight.

    I would use sp_browsereplcmds to see what is in the queue to be replicated. You may find you have defunct subscriptions or snapshot information with metadata still in the replication tables which is not being flushed.

    You might also find that setting immediate sync off will help.

    Wednesday, June 27, 2018 2:41 PM
  • Thanks Hillary! Replication itself is working fine. There is no issue with synch. Also, tracer tokens are getting replicated immediately with latency of 5 secs. Only issue is cleanup is not able to cleanup records from msrepl_transactions table. Immediate synch is off on all my publications. Is is possible that msrepl_transactions have orphaned records that are not present in msrepl_commands table? If yes, how can I find that and is it safe to delete those transactions?
    Wednesday, June 27, 2018 5:13 PM
  • Yes, it is possible

    use sp_browsereplcmds to see if you can see then.

    do not delete snapshot commands.

    Wednesday, June 27, 2018 5:14 PM