使用SQL通知的C# SignalR程序频繁触发Error 28504等错误求助

1. 修复数据库主密钥问题（错误代码32） #

这是核心基础问题，Service Broker必须依赖数据库主密钥才能正常工作。执行以下SQL命令：

USE myDb;
-- 检查并创建数据库主密钥（如果不存在）
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!'; -- 替换为符合要求的强密码（大小写+数字+特殊字符）
-- 用服务主密钥加密数据库主密钥，避免后续需手动输入密码
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

2. 清理残留的对话与临时服务（错误-8490、28504） #

之前测试的Windows应用异常关停后，SQL Server中残留了无效的对话句柄和自动创建的临时服务，导致SignalR连接失败。执行以下SQL清理：

USE myDb;
-- 终止所有错误状态的对话
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE cur CURSOR FOR SELECT conversation_handle FROM sys.conversation_endpoints WHERE state = 'ER';
OPEN cur;
FETCH NEXT FROM cur INTO @handle;
WHILE @@FETCH_STATUS = 0
BEGIN
    END CONVERSATION @handle WITH CLEANUP;
    FETCH NEXT FROM cur INTO @handle;
END
CLOSE cur;
DEALLOCATE cur;

-- 删除所有以SqlQueryNotificationService-开头的临时服务
DECLARE @serviceName NVARCHAR(256);
DECLARE curServices CURSOR FOR SELECT name FROM sys.services WHERE name LIKE 'SqlQueryNotificationService-%';
OPEN curServices;
FETCH NEXT FROM curServices INTO @serviceName;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('DROP SERVICE [' + @serviceName + ']');
    FETCH NEXT FROM curServices INTO @serviceName;
END
CLOSE curServices;
DEALLOCATE curServices;

3. 优化配置避免后续日志爆炸 #

• 代码层面：确保C#中正确释放SqlDependency对象，使用using语句自动回收资源：

using (var sqlConn = new SqlConnection("YourConnectionString"))
using (var command = new SqlCommand("YourQuery", sqlConn))
{
    sqlConn.Open();
    using (var dependency = new SqlDependency(command))
    {
        dependency.OnChange += (sender, e) => { /* 自定义通知处理逻辑 */ };
        command.ExecuteReader();
    }
}

• SQL Server层面：开启自动清理并设置合理的通知超时：

USE myDb;
ALTER DATABASE myDb SET QUERY_NOTIFICATION_AUTO_CLEANUP ON;
ALTER DATABASE myDb SET QUERY_NOTIFICATION_TIMEOUT = 300; -- 超时时间设为5分钟（可根据业务需求调整）

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