sql server Service Broker 相关查询

sql server Service Broker 相关查询

-- 查看传输队列中的消息

--如果尝试从队列中移除时，列将表明哪里出现了问题

select * from sys.transmission_queue

-- 查看Service Broker 激活的存储过程

select * from sys.dm_broker_activated_tasks

-- 查看数据库中的每个会话端点。会话端点代表Service Broker 会话的每一端。

-- 会话端点视图state列显示会话的状态

select * from sys.conversation_endpoints

-----------------------------------------------------------------------

--查看活动队列

SELECT * FROM <queue name> WITH (NOLOCK)

--查找数据库的service_broker_guid

SELECT service_broker_guid

FROM sys.databases

WHERE database_id = DB_ID() ;

--清除无法正常完成的会话

END CONVERSATION @dialog_handle WITH CLEANUP ;

-- 指定数据库应接收新的Broker 标识符。由于该数据库被视为新的Service Broker，所以将立即删除数据库中的所有现有会话，而不生成结束对话框消息。

alter database guoqiang set NEW_BROKER

-------------------------------------------------------------------------

-- 查看每个Service Broker 网络链接

select * from sys.dm_broker_connections

-- 查看实例中的每个队列监视器，队列监视器负责管理队列的激活。

select * from sys.dm_broker_queue_monitors

-- 查看针对每个Service Broker 消息都返回一行，此消息表示SQL Server 实例正在转发中。

select * from sys.dm_broker_forwarded_messages

-- 指定数据库应接收新的Broker 标识符。由于该数据库被视为新的Service Broker，所以将立即删除数据库中的所有现有会话，而不生成结束对话框消息。

alter database guoqiang set NEW_BROKER

-- 指定对指定的数据库启用Service Broker

alter database guoqiang set ENABLE_BROKER

--指定为指定数据库禁用Service Broker

alter database guoqiang set DISABLE_BROKER

----------------------------------------------

-- 消息类型视图

select * from sys.service_message_types

--系统中的约定视图

select * from sys.service_contracts

-- 路由视图

select * from sys.routes

--队列是一种属于架构的对象。队列显示在目录视图

select * from sys.objects

--服务视图

select * from sys.services

--消息和约定的关系视图

select * from sys.service_contract_message_usages

-- 查看消息类型列表、它们使用的约定

SELECT C.name AS Contract, M.name AS MessageType,

 CASE

    WHEN is_sent_by_initiator = 1

     AND is_sent_by_target    = 1 THEN 'ANY'

    WHEN is_sent_by_initiator = 1 THEN 'INITIATOR'

    WHEN is_sent_by_target    = 1 THEN 'TARGET'

 END AS SentBy

FROM sys.service_message_types AS M

 JOIN sys.service_contract_message_usages AS U

    ON M.message_type_id = U.message_type_id

 JOIN sys.service_contracts AS C

    ON C.service_contract_id = U.service_contract_id

ORDER BY C.name, M.name;

--查看队列使用的内部表的名称

SELECT Q.name AS QueueName, I.name AS InternalName

FROM sys.service_queues AS Q

 JOIN sys.internal_tables AS I

    ON Q.object_id = I.parent_object_id;

--查看服务和队列

SELECT S.name, Q.name

FROM sys.services AS S

 JOIN sys.service_queues AS Q

   ON S.service_queue_id = Q.object_id;

----服务和约定的映射关系

sys.service_contract_usages

SELECT S.name AS [Service], Q.name AS [Queue], C.name AS [Contract]

FROM sys.services AS S

 JOIN sys.service_queues AS Q

    ON S.service_queue_id = Q.object_id

 JOIN sys.service_contract_usages AS U

    ON S.service_id = U.service_id

 JOIN sys.service_contracts AS C

    ON U.service_contract_id = C.service_contract_id;

--清除处于错误状态的会话

DECLARE @handle AS UNIQUEIDENTIFIER;

DECLARE conv CURSOR FOR

 SELECT conversation_handle

 FROM sys.conversation_endpoints

 WHERE state = 'ER';

OPEN conv;

FETCH NEXT FROM conv INTO @handle;

WHILE @@FETCH_STATUS = 0

BEGIN

 END Conversation @handle WITH CLEANUP;

 FETCH NEXT FROM conv INTO @handle;

END

CLOSE conv;

DEALLOCATE conv;

SELECT * FROM sys.transmission_queue

SELECT * FROM sys.conversation_endpoints ORDER BY security_timestamp DESC

SELECT * FROM sys.service_queue_usages

SELECT * FROM sys.dm_broker_connections

SELECT * FROM sys.dm_broker_activated_tasks

SELECT * FROM sys.dm_broker_queue_monitors

SELECT * FROM sys.dm_os_performance_counters

SELECT * FROM sys.dm_os_performance_counters WHERE object_name='SQLServer:Broker Statistics'

SELECT * FROM sys.dm_os_performance_counters WHERE object_name='SQLServer:Broker/DBM Transport'

SELECT * FROM sys.dm_os_performance_counters WHERE object_name='SQLServer:Broker Activation'

SELECT * FROM sys.dm_os_performance_counters WHERE object_name='SQLServer:Broker TO Statistics'