Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
imarchenko
Yak Posting Veteran
57 Posts |
 Posted - 2007-10-19 : 17:26:27
|
| Hello! We have setup up transactional replication with dedicated distributor in SQL Server 2005 environment. I have noticed that during particular time of the day latency is increasing dramatically. I have been checking Tracer Tokens and Total latency during that time is around 30-40 min (both publisher to distributor and distributor to subscriber is taking much longer that normal). Normally, it is less than 10 sec. I was wondering if there is a way to pinpoint exact cause of the latency. This is pull subscription. I would appreciate if someone can share (or point to the right direction) best practice on transactional replication setup/maintenance. My understanding is that only committed transactions are replicated, correct? I checked database on publisher and didn't see any outstanding long running transaction. Any help is greatly appreciated.Thanks,Igor |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-19 : 17:47:13
|
| Latency can quickly build up for multiple reasons - more load on publisher, connection issues with either publisher or subscriber etc. Also dont panic by looking at the latency values in the Repl monitor. Its like your CPU %, one sec its 40 min another it comes down to secs.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-19 : 23:43:04
|
| Did you check disk i/o? What kind of network connection used between each of them? Checked network traffic? |
|
|
|
imarchenko
Yak Posting Veteran
57 Posts |
Posted - 2007-10-20 : 15:38:25
|
| Thanks for your replies. One thing on subscriber is that both database file and log file are located on the same physical disk. I think we are paying price for this.Igor |
|
|
|
|
|
|
Troubleshooting transactional replication latency