Troubleshooting SQL Server Service Broker

25 May,2016 by Jack Vamvas

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)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Troubleshooting SQL Server Service Broker


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer