I have a transactional replication publication from the primary server (SQL Server 2008 R2, with the DB in 2000 compatibility) to a read-only copy used for reporting/spreadsheets/etc. Replication is primarily log-based with snapshots on-demand. This publication includes an article for a table of company data (basic stuff: name, address etc).

It was brought to our attention this morning that the data in this table was not being updated on the subscriber when changed on the publisher. We verified that, yes, the table was missing some newly-added rows. Generating a new snapshot fixed that, but further tests show it's still not updating real-time based on the transaction log.

Replication is running, and Replication Monitor is showing data flow between servers with just a few seconds' latency, and so insists that nothing is wrong. The oddest thing is that when I ran a validation on the subscription, I didn't get any result, pass or fail, for the company table (all other tables in the publication passed). It's almost as if the replication process is completely ignoring the table, despite its inclusion in the publication.

The only other clue is that when we generated the new snapshot, the company table errored due to an unsupported column type (varchar(max), not supported by 2000). We fixed the issue by allowing the snapshot to change the affected columns to text types, then manually changing them back, which is a common workaround that's worked fine before. However, replication was recently reinitialized (couple weeks back) for this database between the two servers to add some new tables to the publication, which involved another snapshot generation. There was no problem with that table for that snapshot, and nothing's changed on it since. In fact, the columns that needed to change to legacy types have been in place long before any replication issues were reported.

This one's really baking our noodles; any help tracking down the issue would be appreciated.

EDIT: My sharp-eyed boss caught a message on the subscription status window that the initial snapshot specifically for the company table article wasn't present. We generated another snapshot, and problem solved.

  • There are two pieces to transactional replication: The log reader agent (which picks up changes from the publisher & stages them in the distribution database), and the distribution agent (which takes the changes from the distribution database and sends them to the subscriber database). Have you check the logs for those two agents? – AMtwo Apr 12 '17 at 18:55
  • @AMtwo - We did. The issue turned out to be an issue with the snapshot specifically for that table, the subscriber was not seeing it for some reason and therefore didn't have one to reinitialize that table for replication; another snapshot generation fixed it. – KeithS Apr 12 '17 at 19:01
  • 2
    If you have details on how you figured out that this one table had a bad snapshot, you should post it as an answer! – AMtwo Apr 12 '17 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.