April 2, 2014 at 7:49 pm
Hi,
I am using transactional replication with the replicated subscriber db functioning as a Data warehouse.
Occasionally when I run reports on the subscriber db, it returns an error message about some locking in the db.
When I run sp_lock, I can see that the report session has taken out some S locks on some tables, and the distribution agent has taken out IX TAB locks on some of the same tables.
It seems that the IX TAB locks taken by the distribution agent is blocking S locks being taken out by the report.
I thought the distribution agent was not supposed to be taking out any IX or X locks in the subscriber db at all?
The distribution agent is using the default profile with the following settings:
Default Value Value
-BcpBatchSize 0 2147473647
-CommitBatchSize 100 100
-CommitBatchThreshold 1000 1000
-HistoryVerboseLevel 2 1
-KeepAliveMessages 300 300
-PollingInterval 5 5
I would be grateful for any help provided as this issue is very frustrating.
Thanks in advance.
April 2, 2014 at 8:39 pm
I may have seen the same thing.
Specifically I set up transactional replication on tables that see some usage, sent out a large batch update during the day (I don't leave the jobs running, I start and stop them so I can control when updates go out), and there was an instance where the user application locked up waiting.
Since then we turned on read committed snapshot isolation for those replicated databases and I haven't seen the problem recur since. I'm not suggesting you turn it on, seeing as it could cause a host of issues, but it's an option.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply