SQL Server Service Broker-related queries

SQL Server Service Broker-related queries

--viewing messages in a transport queue--If you try to remove from the queue, the column indicates where the problem occurredSelect *  fromSys.transmission_queue--View service Broker-activated stored proceduresSelect *  fromSys.dm_broker_activated_tasks--view each session endpoint in the database. The session endpoint represents each end of the service Broker session. --session Endpoint View State column shows the status of the sessionSelect *  fromsys.conversation_endpoints-------------------------------------------------------------------------viewing the active queueSELECT *  from <Queue name>  with(NOLOCK)--find the Service_broker_guid of a databaseSELECTservice_broker_guid fromsys.databasesWHEREdatabase_id= db_id() ; --clearing a session that does not complete properlyENDConversation@dialog_handle  withCLEANUP;--specifies that the database should receive the new broker identifier. Because the database is considered a new service Broker, all existing sessions in the database are immediately deleted without generating a closing dialog message. Alter DatabaseGuoqiangSetNew_broker---------------------------------------------------------------------------View each service Broker network linkSelect *  fromsys.dm_broker_connections--to view each queue monitor in the instance, the queue monitor is responsible for managing the activation of the queue. Select *  fromsys.dm_broker_queue_monitors--View returns a row for each service Broker message that indicates that the instance of SQL Server is being forwarded. Select *  fromsys.dm_broker_forwarded_messages--specifies that the database should receive the new broker identifier. Because the database is considered a new service Broker, all existing sessions in the database are immediately deleted without generating a closing dialog message. Alter DatabaseGuoqiangSetNew_broker--Specifies that service Broker is enabled on the specified databaseAlter DatabaseGuoqiangSetEnable_broker--Specifies that service Broker is disabled for the specified databaseAlter DatabaseGuoqiangSetDisable_broker------------------------------------------------Message Type ViewSelect *  fromSys.service_message_types--contract views in the systemSelect *  fromsys.service_contracts--Route ViewSelect *  fromsys.routes--A queue is a schema-owned object. Queues appear in catalog viewSelect *  fromsys.objects--Service ViewSelect *  fromsys.services--relationship view of messages and contractsSelect *  fromsys.service_contract_message_usages--View a list of message types, the conventions they useSELECTC.name asContract, M.name asMessageType, Case     whenIs_sent_by_initiator= 1      andIs_sent_by_target= 1  Then ' any'     whenIs_sent_by_initiator= 1  Then 'Initiator'     whenIs_sent_by_target= 1  Then 'TARGET' END  asSentby fromSys.service_message_types asMJOINSys.service_contract_message_usages asU onm.message_type_id=u.message_type_idJOINSys.service_contracts asC onc.service_contract_id=u.service_contract_idORDER  byc.name, M.name;--View the name of the internal table used by the queueSELECTQ.name asQueueName, I.name asInternalName fromSys.service_queues asQJOINSys.internal_tables asI onQ.object_id =i.parent_object_id; --View services and QueuesSELECTS.name, Q.name fromSys.Services asSJOINSys.service_queues asQ ons.service_queue_id=Q.object_id; ----mapping relationships for services and engagementssys.service_contract_usagesSELECTS.name as [Service], Q.name as [Queue], C.name as [Contract] fromSys.Services asSJOINSys.service_queues asQ ons.service_queue_id=Q.object_id JOINSys.service_contract_usages asU ons.service_id=u.service_idJOINSys.service_contracts asC onu.service_contract_id=c.service_contract_id;--clearing a session in an error stateDECLARE @handle  as uniqueidentifier;DECLAREConvCURSOR  for SELECTConversation_handle fromsys.conversation_endpointsWHEREState= 'ER';OPENConv;FETCH NEXT  fromConv into @handle; while @ @FETCH_STATUS = 0BEGIN ENDConversation@handle  withCLEANUP;FETCH NEXT  fromConv into @handle;ENDCLOSEConv;deallocateConv;SELECT *  fromSys.transmission_queueSELECT *  fromSys.conversation_endpointsORDER  bySecurity_timestampDESCSELECT *  fromsys.service_queue_usagesSELECT *  fromsys.dm_broker_connectionsSELECT *  fromSys.dm_broker_activated_tasksSELECT *  fromsys.dm_broker_queue_monitorsSELECT *  fromsys.dm_os_performance_countersSELECT *  fromSys.dm_os_performance_countersWHERE object_name='Sqlserver:broker Statistics'SELECT *  fromSys.dm_os_performance_countersWHERE object_name='sqlserver:broker/dbm Transport'SELECT *  fromSys.dm_os_performance_countersWHERE object_name='Sqlserver:broker Activation'SELECT *  fromSys.dm_os_performance_countersWHERE object_name='Sqlserver:broker to Statistics'

SQL Server Service Broker-related queries