Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 14 '13 at 8:25

This question came from our site for professional and enthusiast programmers.

1 Answer 1

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.

share|improve this answer
    
As expected, the token is in the distribution database but won't commit to the subscriber. I don't know how to debug the cause of this, though. –  Aushin Apr 11 '13 at 20:28
1  
People think replication is magic. It's not; it's just another application making inserts, updates, and deletes (through stored procedures) in your database. So treat it as such. Is there blocking? A bad query plan (e.g. parameter sniffing) for one or more of the procs? Fire up a trace and see what's happening. That all assumes that there aren't any errors as far as the repl'n monitor is concerned and that the distribution agent is running... –  Ben Thul Apr 11 '13 at 23:00
1  
One other thing to keep in mind; there is a clock ticking. Specifically, the minimum retention property on the distribution database is when repl'n is allowed to start trimming. So you may want to increase that while you troubleshoot. –  Ben Thul Apr 11 '13 at 23:01
    
I know it's not magic, it's just very strange for me. I'm noticing that there are procedures used by replication to actually apply updates, named like: sp_MSupd_dboTableName. But curiously, I do not see any with the table names of the ones I am having trouble with. I think I should probably try to trace the token stored procedure, but I'm having trouble figuring out which procedure processes tokens. –  Aushin Apr 12 '13 at 12:35
1  
I logged a connect item a while back for this very issue (same issue which causes me to know that you need to be subscribed to the first article to get tokens to commit at the subscriber). The response was that it's an unsupported configuration to not subscribe to all articles in a pub. So my advice: either get rid of the unsubscribed to articles, subscribe to them, or move them to their own publication. –  Ben Thul Apr 12 '13 at 22:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.