Troubleshooting SQL Server Service Broker

If you’re not experienced in all the nuances of Service Broker, it can be tricky to diagnose or identify if there is an issue with Service Broker.

There are plenty of products using Service Broker. SCCM is an example .

Here are some queries I use when needing to troubleshoot SQL Server Service Broker.

--Check Service Broker activated stored procedures
SELECT t.*, s.last_wait_type,* FROM sys.dm_broker_activated_tasks t
INNER JOIN sys.dm_exec_requests s ON s.session_id = t.spid

--check to see if  the database is broker enabled
select name , is_broker_enabled from sys.databases

--check if queues are enabled
select name,is_receive_enabled,is_enqueue_enabled from sys.service_queues

--check tcp endpoints for SERVICE BROKER
select * from sys.tcp_endpoints

--list out SCCM sites
Select * from Sites 

--Object Lock States for “Serialized Editing of Data Objects”
select * from SEDO_LockState where LockStateID = 1

--Check Service Broker network connections
select * from sys.dm_broker_connections

--List Queue Monitors 
select * from sys.dm_broker_queue_monitors

--ConfigMgr 2012 and DRS Initialization troubleshooting
select * from vLogs where LogTime > GETDATE()-1 and ProcedureName <> 'spDRSSendChangesForGroup' order by LogTime DESC

Change the port for sql server service broker (SQL Server DBA)