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..