SB Not releasing locks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mitch - 24 Sep 2007 23:08 GMT

Hello, we have Service Broker running for one of our applications. We keep
running into problems where occassionally it seems to lock up and no messages
get sent. And it seems to be holding exclusive locks on the target queue as
well as some internal table it uses, and these locks don't seem to ever go
away. Thus, you cannot select * from the target queue. We end up having to
kill the spid that is holding the locks. Any ideas why this spid is holding
locks and not letting go? Any help would be appreciated!

Thanks,
Mitch

Reply to this Message

Adam Machanic - 28 Sep 2007 01:03 GMT

What process is receiving messages from the queue? Do you use activation?
If so, check out the sys.dm_broker_activated_tasks view and see if your
activation procedure is not closing properly. It sounds like something on
the receive end is holding open a transaction and neither committing it nor
rolling it back in a timely fashion. I don't think this is Service Broker
itself.

Signature

Adam Machanic
SQL Server MVP - http://sqlblog.com

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220

> Hello, we have Service Broker running for one of our applications. We
> keep
[quoted text clipped - 11 lines]
> Thanks,
> Mitch

Reply to this Message