July 24, 2009 at 6:29 am
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
July 24, 2009 at 8:26 am
July 24, 2009 at 8:35 am
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
July 24, 2009 at 9:13 am
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.
July 24, 2009 at 8:29 pm
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 ...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply