I sent a tracer token through a replication job that was reporting about 2 seconds of latency about two and a half hours ago. I sent the token through because I noticed a row that was inserted on a published table had not been sent to the replicated table.
I'm not sure what this indicates and would appreciate any help in troubleshooting it. Thank you.
UPDATE:
My replication publication hasabout 30 tables. Most of these tables are being replicated as expected. When I run a trace I can see their sp_MSupd_dboXXXXX procedures running, I can verify that the data is in sync.
However, for several tables, this is not happening. When I looked in the database the table was being replicated to, it did not even have its own sp_MSins, upd, and del procedures.
When I run sp_browsereplcmds and provide its publication_database_id and article_id, I can see that the command is waiting to be run.
For example, it might say {CALL sp_MSins_dboTableName (fields here)}. It will sit there for a while, then disappear.
My first assumption was that it wasn't being applied because the procedure didn't exist. So I wrote my own sp_MSins_ proc to confirm this (@c1 datatype, @c2 datatype etc…) and tested it to be sure it worked.
But even then, when I do an insert, the command sits in the repl_commands table for a while, then disappears, and no row ever hits my table.
I'm getting pretty lost here. Can anyone help? Thanks!
Some more detail:
The distribution DB lives on the subscriber. There are several publications from the same database on the publisher to the same database lon the subscriber (three to be exact, two work fine, one does not).
On two of the publications, a tracer token is processed within about 4 seconds. On the one I'm having trouble with, the tracer token hangs in limbo indefinitely.
Best Answer
For my money, tracer tokens are a really good way to monitor latency. I don't know what the latency metric in replication monitor is measuring, but I've personally found it to be unreliable. For my environment, I post a tracer token through a SQL Agent job and then monitor the token's progress by looking at the token tables in the distribution database.