Transaction Replication Latency

Question

Transaction Replication Latency

The Natrix

SSC Eights!

Points: 835
More actions
September 1, 2010 at 4:26 pm

#239804

Hi everyone,
I have Transactional Replication with a push subscription setup on SQL Server 2008. I'm able to generate the snapshot successfully. All the agents seem to be working fine but the latency is 4 hours after executing for 1 day and it doesn't seem like its catching up. I deleted everything including the distribution database and started from scratch, but I have the same problem. Can anyone point me in the right direction? Thanks.
I also noticed that my transaction log doesn't increase while its bulk copying, should this be a cause for concern?

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

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Check the Replication Monitor for the reason for the latency. You can also check the log-reader and distribution agent job history.

Transaction log size should not be cause of concern.

crazy4sql SSCoach Points: 19590 More actions · Answer 2

if you run below query on the serevr where u have the distributor database, you will come to know the errors its generating.

use distribution

go

select * from MSrepl_errors order by time desc

then in activity monitor you will get some unique command seq number, and the detail of that command you can get by

use distribution

go

sp_browsereplcmds 'unique hexadecimal number', 'same unique hexadecimal number'

----------
Ashish

caladba SSCommitted Points: 1514 More actions · Answer 3

Here's a great article that can pretty much take you step by step on trouble shooting transactional replication performance.

http://blogs.msdn.com/b/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx

The Natrix SSC Eights! Points: 835 More actions · Answer 4

Thanks to everyone that responded for their tips and advice.

Brucla its the article you referred me to that I had found online a short while before that really helped me. Turns out the latency was with my Log Reader agent. We're supposed to redo a snapshot and start the agents again today, so we'll see how that goes...