September 11, 2014 at 8:31 am
We have a subscriber database that is progressively becoming more latent throughout the day. Starting each day close to real time, and ending each day 5-7 hours behind. We are using transactional replication and have 5 published databases on server 'A'. The distribution database and the 5 subscription databases on server 'B', all using pull subscriptions. All of the subscription databases share the same data drive and share a different drive for the log files. The tempdb files have their own drive. Of the 5 published databases there are 2 database that are the most heavily used and the largest. One is giving us the problems I have described and the other one is fine(mostly real-time, some short delays of a minute or 2). Here are some stats from the distributor:
************************ STATISTICS SINCE AGENT STARTED ***********************
09-11-2014 08:58:29
Total Run Time (ms) : 64089860 Total Work Time : 40687301
Total Num Trans : 2395852 Num Trans/Sec : 58.88
Total Num Cmds : 2840887 Num Cmds/Sec : 69.82
Total Idle Time : 23390582
Writer Thread Stats
Total Number of Retries : 0
Time Spent on Exec : 22396738
Time Spent on Commits (ms): 60614 Commits/Sec : 0.24
Time to Apply Cmds (ms) : 40687301 Cmds/Sec : 69.82
Time Cmd Queue Empty (ms) : 18153624 Empty Q Waits > 10ms: 30189
Total Time Request Blk(ms): 41544206
P2P Work Time (ms) : 0 P2P Cmds Skipped : 0
Reader Thread Stats
Calls to Retrieve Cmds : 9405
Time to Retrieve Cmds (ms): 24985929 Cmds/Sec : 113.70
Time Cmd Queue Full (ms) : 14712663 Full Q Waits > 10ms : 14465
Any suggestions? Please. I also have some trace file data.
Thanks,
DK
December 17, 2014 at 4:15 am
Recently when I was on a visit to the USA one of my customers mentioned the same problem. After a thorough investigation, it was noticed that the N/W through which the Data was travelling itself had a huge amount of traffic on it causing a delay of 4+ hours on the Replication side. They have now implemented a seperated dedicated N/W for the same and since then Replication is running fine without any issues.
With Thanks,
Satnam
May 7, 2015 at 10:42 am
can you provide below details
what is the latency b/w publisher and distribution database -- if the issue is this keep distribution DB on a separate server may help or move to a dedicated disk which can give better IO. Check data retention period.
what is the latency b/w subscriber and distribution database
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy