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