SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional replication distribution agent is locking tables in the subscriber


Transactional replication distribution agent is locking tables in the subscriber

Author
Message
peter945
peter945
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 58
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.
Cody Konior
Cody Konior
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 1105
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search