October 3, 2012 at 12:11 pm
We have a oltp system with 3 databases which are replicated using transactional replication to a separate reporting server. Each database has several dozen publications with usually 1 article. Each publication can have multiple subscribers but usually only 1. There is a remote distributor.
We are currently purging a large amount of data from one of the replicated databases. Close to 100 publications would be impacted by the deletes which are done on a record by record basis.
We have seen distribution latency increase significantly at times and after drilling into it a bit we found that the log reader while writing to the distribution database is experiencing a lot of slowness with executing sp_MSadd_replcmds. It doesn't run slowly all the time but when the latency increases the log readers (there are 3 of them) are all executing sp_MSadd_replcmds. The processes are stuck with a waits of LCK_M_IX. In running sp_lock for the spids that are waiting we see that they are attempting to take tab lock with a mode of IX on the MSrepl_transactions table.
More specifically when sp_MSadd_replcmds is being executed the command which is stalling is:
INSERT INTO MSrepl_transactions
VALUES (@publisher_database_id, @xactId, @xactSeqNo, @date)
So my questions are has anyone else seen this type of wait behavior with the log reader agent? Any thoughts on how to correct it? Why would a simple insert require a table lock?
October 3, 2012 at 12:59 pm
I am by no means a replication expert but I've seen similar issues when the distribution cleanup job starts to fail. Lowering the distribution retention period also helped but my system allowed for a retention period of around 12 hours I believe and the default is 72?? I'd have to verify but it sounds right.
October 3, 2012 at 3:29 pm
It was the distribution clean up job blocking the log reader. I fixed the slowness using the steps from this article.
http://www.replicationanswers.com/TransactionalOptimisation.asp
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply