Killing A Service Broker SPID

MarlonRibunal, 2018-03-06 (first published: 2018-02-20)

Killing a SPID shouldn’t be complicated. Execute the command KILL [SPID] and that should kill the session. But there are situations where a SPID that is spawned by the Service Broker stays alive even after the queue is stuck in limbo.

No matter how many times you kill the SPID, it will find a way to re-connect and will continue to sap your CPU. You would think that a server reboot would kill the SPID as the reboot will restart the service. But, no, the SPID will restart again. Setting the database to SINGLE_USER? Nope. That does not work either.

Ok, this is more of a note to self than a real blog post.

Here’s a quick way to KILL a SPID spawned by a Service Broker queue.

Run sp_whoisactive to check the program_name of the running SPID. The program name should give you the identification of the running process.

Find Queue Name

SELECT qs.name,
qm.tasks_waiting
FROM sys.dm_broker_queue_monitors qm
JOIN sys.service_queues qs
ON qm.queue_id = qs.object_id

Then, get all the Conversation_Handle of the Queue

SELECT Conversation_Handle FROM dbo.MyQueueNameHere

That gives us all the Conversation_Handle of the Queue, then we can then End the conversations, like,

END CONVERSATION 'xxxxxxx-xxxx-xxx-xxx-xxxxxxxxxx'

Now we can go back to the active tasks of the Queue to get all the SPIDs

SELECT SPID FROM sys.dm_broker_activated_tasks

Then, based on that list, we can then issue the KILL command.

KILL xx;

Again, you cannot just KILL SPID or end any Service Broker conversation willy-nilly. You have to do your due diligence before deciding to kill the SPIDs or end the Service Broker tasks.

Here’s a list of useful DM and Catalog Views that are related to Service Broker:

sys.dm_broker_queue_monitors
sys.dm_broker_activated_tasks
sys.dm_exec_background_job_queue
sys.services
sys.service_queues
sys.service_queue_usages
sys.service_contract_message_usages
sys.transmission_queue

You can do something like this to get more info about the queues and the tasks or processes associated with them

SELECT at.procedure_name,
s.session_id,
s.login_time,
s.last_request_start_time,
s.status,
s.cpu_time,
s.memory_usage
FROM sys.dm_broker_activated_tasks AS at
JOIN sys.dm_exec_sessions s
ON at.spid = s.session_id

The post Killing A Service Broker SPID appeared first on SQL, Code, Coffee, Etc..

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads