Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Transactional replication distribution agent is locking tables in the subscriber Expand / Collapse
Posted Wednesday, April 2, 2014 7:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 8:12 PM
Points: 1, Visits: 58

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.
Post #1557781
Posted Wednesday, April 2, 2014 8:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 6, 2016 11:50 PM
Points: 247, Visits: 1,104
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.

Post #1557788
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse