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
