Replication latency troubleshooting

Question

Post reply

Replication latency troubleshooting

Saurabh Aggarwal

Ten Centuries

Points: 1073
More actions
July 24, 2009 at 6:29 am

#208672

Replication latency troubleshooting:-
In my enviornment we have created a multiple publications on each table level in a database...
means each table will have single publication and these are really very big tables...
its very hard for me to troubleshoot,
Question are
1. when there is a latency its dificult for me to check coz of which publication/table the latency is there
2. how can i find that due to which big transaction the replication latency is there
3. how much data/transactions is need to apply at subscriber and how long will it take so that latency will come down.
4. whats the replication volume for previous hour
5. can i use below stored procs for any troubleshooting
sp_showbrowsereplcmds
sp_repltrans
sp_replcmds
sp_replshowcmds

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 1

Have you looked at replication monitor? That should show you all of the information that you're looking for.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

Saurabh Aggarwal Ten Centuries Points: 1073 More actions · Answer 2

i have seen repl monitor but i dont think that it will show that which publ is causing latency, there are approx 30 publ and its difficult to check each publ one by one

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 3

Saurabh Aggarwal (7/24/2009)
Replication latency troubleshooting:-
In my enviornment we have created a multiple publications on each table level in a database...
means each table will have single publication and these are really very big tables...
its very hard for me to troubleshoot,
Question are
1. when there is a latency its dificult for me to check coz of which publication/table the latency is there
2. how can i find that due to which big transaction the replication latency is there
3. how much data/transactions is need to apply at subscriber and how long will it take so that latency will come down.
4. whats the replication volume for previous hour
5. can i use below stored procs for any troubleshooting
sp_showbrowsereplcmds
sp_repltrans
sp_replcmds
sp_replshowcmds

My last response was submitted hastily. Here's a better answer:

1. At best Replication Monitor shows this for each publication. If you want to see a summary of all subscribers across all publictions run the undocumented stored procedures Sp_MSenum_distribution (SQL 2000\2005\2008) or sp_MSenum_replication_agents (SQL 2005\2008).

2. Best bet is to query MSdistribution_agents, MSsubscriptions, MSdistribution_history, MSrepl_transactions, and MSrepl_commands in the distribution database. I don't have a query for this but I could write one if I get some time.

3. In Replication Monitor double click an individual subscription and click the Undistributed Commands tab. Under the covers this is calling sp_replmonitorsubscriptionpendingcmds in the distribution database.

4. Sp_MSenum_distribution and sp_MSenum_replication_agents shows this information for each distribution agent's current session. (This is another good reason to run your agents continuously; if you run every minute these values reset every time)

5. Depends on what you're trying to troubleshoot. Give some specific examples please.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

Saurabh Aggarwal Ten Centuries Points: 1073 More actions · Answer 4

my replication is on sqlserver 2000, and yestrday we face one issue of latency and app team was asking the details. but i was not sure coz of which table/publ the latency is there and why the latency is there ... there is only one publisher having all publications and one subscriber ...