sql server Service Broker 相关查询
时间：2021-07-01 10:21:17
帮助过：2人阅读
查看传输队列中的消息
--如果尝试从队列中移除时，列将表明哪里出现了问题
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‘
sql server Service Broker 相关查询
标签：
