I am not going to spend a lot of time on this post nor did I go through a big analysis as to the root of “Why”. I am just going to describe the symptoms and how I fixed it. I got a call from one of my users stating that the replicated source and destinations where out of synch. I immediately looked at the replication monitor and saw no errors on any agents and no latency??? I then ran a data compare on the source and destination tables and did in fact see that changes to the production environment were not getting replicated to the replica. I then check all the agent jobs for errors and nothing. The replication just was not picking up any transactions… very odd, although I have seen this on rare occasions where for no apparent reason replication just stops working. It was a relatively small database and only a few tables were being replicated so I decided to run the snapshot agent. Ran fine no errors reported yet the tables where still out of sync. I figured ok, I’ll drop and recreate the replication (publication and subscription) but I really did not want to do that without knowing the cause. This has worked in the past but it just bothered me. So I decided to check one last thing, I performed a schema comparison and sure enough there was a schema difference on one column on one of the replicated tables (Text VS Varchar(500)). Not incompatible data types but yet it did represent a difference. The next question was why, as it turns out a vendor had performed an application upgrade the previous week. Apparently the upgrade involved some schema changes. I thought to myself that this is probably the smoking gun.
I altered the table column to match that of production (Text) and re-ran the snapshot agent. Low and behold the data comparison came back clean with no differences. I then monitored replication for then next day or so and everything was fine. I just found it odd that replication complained in no way about the schema change nor was the schema change replicated, and that it effected the entire publication (all three tables). It just reported “no transactions to process”. Oh well chalk it up to Microsoft, there may have be an error reported someplace, maybe in the distributor database, but not by any of the usual suspects.