October 19, 2007 at 3:24 pm
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
October 22, 2007 at 6:08 am
In my opinion, "Transactional Replication" was a poor name to use in the database world. When you think of transaction, a DBA will immediately think of BEGIN TRAN and COMMIT TRAN (if they don't, get a new DBA). Unfortunately, that is not really the meaning of transactional replication.
In transactional replication, you have a Log Reader Agent that reads the database Transaction Log (hence the bad name). When a transaction for an article that is published, the Log Reader Agent tells the Distribution Agent. The Distribution Agent applies any column or row filters specified and passes the information along to the Subscriber (via stored procedure, direct SQL call, or DTS/SSIS package).
Yes, you will only get committed transactions (because only committed transactions get written to the transaction log). When are committd transactions written to the log? When a Checkpoint is reached (which is basically arbitrary). So, first make sure there is a checkpoint, make sure database backups are not running during your long latency period, and make sure your transaction log is not growing or shrinking at the time.
October 22, 2007 at 2:51 pm
Thanks Michael!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply