SQL Server升级2017后Service Broker队列多次无日志自动禁用

队列自发禁用是SQL Server Service Broker内置毒消息检测机制触发，结合你环境的异常点，根因分三层：

1. 触发自动禁用的直接条件：队列开启了POISON_MESSAGE_HANDLING（你当前队列配置为STATUS=ON），当激活存储过程连续5次在接收消息后回滚事务，队列监控进程会自动将队列置为禁用状态，不需要外部操作，这个行为是产品原生设计，不是无理由异常。
2. 触发连续回滚的核心原因：你捕获到的Process ID 57 attempted to unlock a resource it does not own: METADATA: ... CONVERSATION_ENDPOINT_RECV是SQL Server 2017 CU29版本存在的Service Broker并发访问已知引擎bug，当队列并发读线程数较高、同时存在跨事务上下文的CLR调用时，锁分区下的会话端点元数据锁所有权校验会出现时序错误，直接将当前事务标记为doomed状态（XACT_STATE()=-1），你看到的The current transaction cannot be committed and cannot support operations that write to the log file就是事务进入doomed状态后的连锁报错。
3. 放大故障的辅助因素：升级到2017兼容级别后，CLR输出结果插入目标表的语句基数估计偏差，导致出现大量LCK_M_IX锁等待，拉长了事务持有锁的时间，进一步提高了元数据锁时序冲突的概率；你现有激活存储过程的错误处理逻辑，在遇到doomed事务回滚后直接抛出错误退出存储过程，没有阻断连续回滚的计数，累计5次就直接触发了队列禁用。你看到的部分日志行message_body为NULL，就是事务回滚时错误日志插入操作未完整提交的表现。

• 查询系统视图验证触发逻辑：执行SELECT name, is_receive_enabled, is_enqueue_enabled, activation_procedure, last_activated_time FROM sys.service_queues WHERE name = 'ODS_TargetQueue'确认队列当前状态，再查询SELECT * FROM sys.dm_broker_queue_monitors WHERE queue_id = OBJECT_ID('svcBroker.ODS_TargetQueue')，该DMV会记录队列监控的任务状态、最近10次激活/回滚的计数，可直接对应两次队列禁用时间点的连续回滚次数。
• 双机对比验证兼容性影响：你有两台同负载服务器，可将其中一台的数据库兼容级别临时回退到110（SQL Server 2012级别），对比两台服务器上CLR插入语句的执行计划、LCK_M_IX等待时长、CONVERSATION_ENDPOINT_RECV锁报错的出现频率，确认2017兼容级别的基数估计变化对锁冲突的影响程度。
• 补全扩展事件捕获项：在现有捕获严重级别11以上错误的基础上，增加broker_queue_disabled、broker:conversation_group_lock_acquired、sql_transaction_rollback三个事件，不要等队列禁用后再查日志，直接捕获每次事务回滚对应的错误栈、会话ID、持锁时长，定位回滚的直接触发点。
• 排查CLR组件兼容性：检查ParseRequestMessages调用的CLR程序集在SQL Server 2017下的运行权限、事务上下文绑定逻辑，确认是否存在CLR代码未捕获的访问异常直接中断外层事务的情况。

• 第一优先级修复引擎bug：升级SQL Server 2017到最新累积更新版本，你遇到的CONVERSATION_ENDPOINT_RECV元数据锁所有权校验错误，是2017 CU31之前版本在高并发Service Broker场景下的已修复问题，升级后可直接消除最核心的doomed事务触发源。
• 临时阻断队列自动禁用逻辑：执行ALTER QUEUE [svcBroker].[ODS_TargetQueue] WITH POISON_MESSAGE_HANDLING (STATUS = OFF)关闭毒消息自动禁用，该操作无额外性能开销，仅取消连续5次回滚自动禁用队列的逻辑，不会影响消息正常接收处理，待引擎bug修复、业务稳定后可按需重新开启。
• 修正激活存储过程错误处理逻辑：将现有doomed事务回滚后直接RAISERROR退出的逻辑调整为：回滚完成后等待100-200ms，清空临时表变量后重新进入消息接收循环，避免短时间内连续5次回滚触发毒消息检测；如果确需退出存储过程，退出前主动执行ALTER QUEUE [svcBroker].[ODS_TargetQueue] WITH STATUS = ON重置队列状态，不要依赖队列监控的自动处置。
• 优化并发与锁冲突：将队列MAX_QUEUE_READERS从30下调到8-10，你的业务是纯INSERT写入的小时分表场景，10个并发读线程完全可以满足吞吐量需求，降低并发数可以大幅减少元数据锁时序冲突的概率；针对CLR插入语句添加OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))提示，修正2017兼容级别下CLR表值函数的行数估计偏差，消除不必要的LCK_M_IX表级锁等待。
• Query Store调整：将Query Store从只读模式改回读写模式，强制固定CLR插入语句的稳定执行计划，解决之前周一固定出现的错误执行计划问题，不要因为队列问题完全关闭Query Store的计划固定能力。

内容的提问来源于stack exchange，提问作者mbourgon