DROP SERVICE takes a long time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WG - 09 Oct 2007 18:48 GMT

We've been using Service Broker for some time now, and have been very happy
with it, but yesterday we came across an issue that has us stumped. In
testing an upcoming application, we preloaded 700K messages on a TargetQueue,
then processed the data, but to try and get better performance out of the DB,
I wanted to move my Queues to a different filegroup. When I execute a DROP
SERVICE on the Queue to allow me to move it, the DROP SERVICE never returns
(or at least I let it run for an hour anyway)... At the time I executed the
DROP SERVICE, there were no messages on the Initiator or Target that needed
to be cleaned up.. I ran a sp_who2 against the SPID and the Disk I/O is very
active (approximately a 1K I/Os a second on our cluster), so I'm curious, how
do I drop this service and what's going on behind the scenes when the DROP
SERVICE is called? Thanks in advance to anyone with ideas...

Wayne Gadberry

Reply to this Message

Kent Tegels - 09 Oct 2007 22:08 GMT

Hello Wayne,

Dropping a service explicitly deletes any messages for that service from
the underlying queue. Did "processing the data" expunge the queue?

You don't have to drop the queue to re-source its queues. You can simply
ALTER SERVICE [name] ON QUEUE [new_queue_name] to move it.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Reply to this Message

WG - 09 Oct 2007 22:18 GMT

Thanks for the reply Kent.. I found my issue.. I ended my conversations on
the target, but simply RECEIVE'd them off my initiator queue without ending
the conversation (missed that on my side), so the conversations were still in
the DB and DROP SERVICE was having to clear those before it could drop the
service.... I found all my endpoints buried in sys.conversation_endpoints
and am clearing those out now... Think that will fix my issue.. Thanks
again for the reply..

Wayne

> Hello Wayne,
>
[quoted text clipped - 8 lines]
> DevelopMentor
> http://staff.develop.com/ktegels/

Reply to this Message