T-SQL创建和管理Service Broker队列

原创于 2025-08-23 09:24:03 发布 · 466 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #java #sqlserver #大数据

数据库专栏收录该内容

4 篇文章

订阅专栏

文章目录

使用 T-SQL 可以直接创建和管理 SQL Server Service Broker 队列（Queue）。队列是 Service Broker 的核心组件，用于存储和管理消息。以下是创建、配置、查看和维护队列的完整 T-SQL 操作指南：

一、创建队列（CREATE QUEUE）

创建队列是使用 Service Broker 的基础步骤，队列会自动关联一个内部表用于存储消息。

基本语法：

CREATE QUEUE [数据库名.]队列名
[ WITH 
    [ STATUS = { ON | OFF } ] -- 队列是否启用（默认 ON）
    [ , RETENTION = { ON | OFF } ] -- 消息处理后是否保留（默认 OFF）
    [ , ACTIVATION ( 
        [ STATUS = { ON | OFF } ] -- 是否启用自动激活
        [ , PROCEDURE_NAME = 存储过程名 ] -- 处理消息的存储过程
        [ , MAX_QUEUE_READERS = 最大并发数 ] -- 最多同时运行的存储过程数
        [ , EXECUTE AS { SELF | '用户名' | OWNER } ] -- 执行存储过程的身份
    ) ]
    [ , POISON_MESSAGE_HANDLING ( STATUS = { ON | OFF } ) ] -- 是否启用毒消息处理（默认 ON）
]
[ ON { 文件组名 | DEFAULT } ] -- 队列存储的文件组
;

示例 1：创建基础队列

-- 创建一个简单的队列，使用默认配置
CREATE QUEUE OrderProcessingQueue;

示例 2：创建带自动激活的队列

-- 创建队列，配置自动激活（有新消息时自动触发存储过程处理）
CREATE QUEUE OrderProcessingQueue
WITH 
    STATUS = ON, -- 启用队列
    ACTIVATION (
        STATUS = ON, -- 启用自动激活
        PROCEDURE_NAME = dbo.ProcessOrderMessage, -- 处理消息的存储过程
        MAX_QUEUE_READERS = 5, -- 最多5个并发处理进程
        EXECUTE AS SELF -- 以创建者身份执行
    ),
    POISON_MESSAGE_HANDLING (STATUS = ON) -- 启用毒消息处理（默认）
ON PRIMARY; -- 存储在PRIMARY文件组

二、查看队列信息

使用系统视图可查看队列的配置、状态和消息数量。

查看所有队列的基本信息

SELECT 
    name AS 队列名,
    is_activation_enabled AS 自动激活状态,
    activation_procedure AS 激活存储过程,
    max_readers AS 最大并发数,
    is_poison_message_handling_enabled AS 毒消息处理状态,
    is_receive_enabled AS 接收状态
FROM sys.service_queues;

查看队列中的消息数量

SELECT 
    name AS 队列名,
    queued_message_count AS 待处理消息数,
    activation_failure_count AS 激活失败次数
FROM sys.dm_broker_queue_monitors;

三、修改队列（ALTER QUEUE）

可修改队列的配置（如启用/禁用自动激活、调整并发数等）。

基本语法：

ALTER QUEUE [数据库名.]队列名
WITH 
    [ STATUS = { ON | OFF } ] -- 启用/禁用队列（OFF时无法接收消息）
    [ , RETENTION = { ON | OFF } ] -- 修改消息保留策略
    [ , ACTIVATION ( 
        [ STATUS = { ON | OFF } ]
        [ , PROCEDURE_NAME = 新存储过程名 ]
        [ , MAX_QUEUE_READERS = 新最大并发数 ]
        [ , EXECUTE AS { SELF | '用户名' | OWNER } ]
    ) ]
    [ , POISON_MESSAGE_HANDLING ( STATUS = { ON | OFF } ) ]
;

示例：修改队列的自动激活配置

-- 调整最大并发数为3，并禁用自动激活
ALTER QUEUE OrderProcessingQueue
WITH 
    ACTIVATION (
        STATUS = OFF, -- 临时禁用自动激活
        MAX_QUEUE_READERS = 3 -- 调整并发数
    );

四、从队列接收消息（RECEIVE）

使用 RECEIVE 语句从队列中读取并处理消息（读取后消息默认从队列中移除）。

基本语法：

RECEIVE [ TOP(n) ] -- 接收前n条消息
    列名1, 列名2, ... -- 需获取的消息属性（如message_body、conversation_handle）
FROM 队列名
[ WHERE 条件 ] -- 筛选消息
;

示例：接收并处理消息

DECLARE 
    @dialog_handle UNIQUEIDENTIFIER, -- 对话句柄（用于标识消息所属会话）
    @message_body XML, -- 消息内容
    @message_type_name NVARCHAR(128); -- 消息类型

-- 从队列接收1条消息
RECEIVE TOP(1)
    @dialog_handle = conversation_handle,
    @message_body = message_body,
    @message_type_name = message_type_name
FROM OrderProcessingQueue;

-- 处理消息（示例：打印订单信息）
IF @message_type_name = '//MyApp/OrderMessage'
BEGIN
    PRINT '处理订单: ' + CAST(@message_body AS NVARCHAR(MAX));
    -- 处理完成后结束对话（可选，根据业务需求）
    END CONVERSATION @dialog_handle;
END

五、清除队列消息（TRUNCATE/DELETE）

清空队列所有消息（快速）

-- 清空队列（无法回滚，慎用）
TRUNCATE QUEUE OrderProcessingQueue;

删除指定条件的消息（灵活）

-- 删除特定会话的消息（需结合系统视图）
DELETE FROM dbo.OrderProcessingQueue -- 队列对应的内部表（名称与队列相同）
WHERE conversation_handle = '对话句柄值';

六、删除队列（DROP QUEUE）

当队列不再需要时，可删除队列（需确保队列无关联的服务，否则需先删除服务）。

-- 删除队列（若队列关联服务，需先删除服务）
DROP QUEUE OrderProcessingQueue;

七、关键配置说明

自动激活（Activation）
当队列中有新消息时，SQL Server 会自动启动指定的存储过程处理消息，无需手动执行 RECEIVE。适合无人值守的场景。
毒消息处理（Poison Message Handling）
若一条消息连续 5 次处理失败（事务回滚），Service Broker 会自动禁用队列（STATUS = OFF），防止资源耗尽。此时需手动处理毒消息后重新启用队列。
消息保留（Retention）
设为 ON 时，消息处理完成后仍会保留在队列中（标记为已处理），可用于审计或重放，但会增加存储开销。