Replication - Remove Many Pending Commands RRS feed

  • Question

  • I use transactional replication among 4 sql servers to keep data synchronized (Peer-to-Peer).  The databases are highly transactional.  This morning, one of the servers was unable to be reached for period of time.  The result is this (calling them Servers A, B, C, and D):

    If transactions occur on B, C, or D, they successfully replicate to ALL servers, including A

    If transactions occur on A, they do not replicate anywhere.

    What I found when looking in the Replication Monitor, Selecting the Publication on Server A and right clicking a subscription in "All Subscriptions" tab, choosing "View Details" and then the "Undistributed Commands", is that the "Number of commands in the distrubution database waiting to be applied to this Subscriber" is 1.2 million.

    If I start the distribution agent on these subscriptions, it takes a lock that blocks all the other servers, and undistributed commands for those servers/subscriptions begins to accumulate.  It seems like the time to get through 1.2 million commands will take a long time, and i'm concernd the other publications/subscriptions will accumulate a large number as well.

    I would like to "reinitialize" these subscriptsions, but i cannot do this from a backup or snapshot.  The database cannot be taken offline, or altered like that.  What's more, i have verified that the data in the database on all 4 servers is pretty much synchronized. (which makes me wonder what these commands are).

    Essentiall what I would like to do is clear the pending commands, and have replication continue.  I can fix any discrepancies that might remain manually.

    Is there a way to accomplis this?



    Friday, April 12, 2013 6:29 PM


All replies