Clear undistributed commands


  • Hello All,<o:p></o:p>

    To fix data discrepancy in transactional replication, I have to clear up all the
    undistributed transactions and then run RedGate data compare tool to manually
    add/update/delete the records. <o:p></o:p>

    How to I clear up the undistributed transactions from the distributor?<o:p></o:p>

    Can I do that? Will this method sync up the data? I am going to choose to do it off
    hours. But do I have to stop the log reader and distribution agent? I will do this individually for each publisher in a database. Also the subscriber is again a publisher to another subscriber. (A->B->C). Do I have to be concerned about it?

    Tuesday, June 11, 2013 6:04 PM


All replies

  • why do you want to manually delete the rows from msrepl_transactions and msrepl_commands table. the dist cleanup job will automatically cleans these once the data is replicated or reached the retention period.

    The question is why there is data discrepancy? how is the subscribers initialized?

    Tuesday, June 11, 2013 9:31 PM
  • Sounds like you are looking for a cleanup. Your reasons are not much clear but we sometimes use

    EXEC dbo.sp_MSdistribution_cleanup @min_distretention = X, @max_distretention = X
    (@max_distretention determines the cutoff date, specified in hours).

    Tread softly, take backups and test if possible before running this in production environment.

    Wednesday, June 12, 2013 12:09 AM
  • You should not manually be cleaning up the data.  If the data somehow got out of sync, you should reinitiaize it from a snapshot.

    SQL Server is extremely good at keeping the data in sync.  The only reason I have seen it get out of sync in years is someone deleted data from a subscriber instead of the publisher by mistake.

    Wednesday, June 12, 2013 7:52 PM
  • I should ask, why are you pushing from A->B->C instead of just A->B, A->C?

    Wednesday, June 12, 2013 7:53 PM