Service Broker 消息在大约一天后开始挂断

问题描述

我有一个使用 Service Broker 的应用程序是 SQL 2008.大约每天一次，数据库的性能开始受到明显影响，我确定这是因为 Service Broker.如果我使用以下命令硬重置所有代理连接:

I have an application that is using the Service Broker is SQL 2008. About once a day the database's performance starts take a noticeable hit and I have determined that this is because of the Service Broker. If I hard reset all broker connections using the following commands:

ALTER DATABASE [RegencyEnterprise] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [RegencyEnterprise] SET ONLINE

然后性能恢复正常，直到大约第二天.我还注意到，当性能不佳时，运行以下查询会返回大量(目前大约 1000 个)卡在 STARTED_OUTBOUND 状态的对话:

Then the performance returns to normal until about the next day. I have also noticed that when performance is poor, running the following query returns a large number (around 1000 currently) of conversations that are stuck in the STARTED_OUTBOUND state:

SELECT * FROM sys.conversation_endpoints

此外，以下查询不会返回其中的任何条目:

Also, the following queries don't return any entries in them:

SELECT * FROM sys.dm_qn_subscriptions
SELECT * FROM sys.transmission_queue

在此查询返回大量项目的情况下，性能似乎还不错.出现问题的唯一时间是存在 STARTED_OUTBOUND 连接停留在此状态时.

Performance seems to be alright where there are plenty of items returned by this query. The only time when there are problems are when there are connections that are STARTED_OUTBOUND that stay stuck in this state.

我对 SQL Server 2008 实例上的 Service Broker 所做的唯一配置是运行以下命令:

The only configuration I have done to the Service Broker on my SQL Server 2008 instance was to run the following command:

ALTER DATABASE RegencyEnterprise SET ENABLE_BROKER

翻阅 SQL 错误日志，我也发现这个条目超过 1000 次:

Digging through the SQL error log, I have found this entry over 1000 times as well:

07/11/2013 01:00:02,spid27s,Unknown,The query notification dialog on conversation handle '{6DFE46F5-25E9-E211-8DC8-00221994D6E9}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-cb4e7a77-58f3-4f93-95c1-261954d3385a&apos; because it does not exist.</Description></Error>'.

我在整个日志中也看到了十几次这个错误，但我相信我可以通过在数据库中创建一个主密钥来解决这个问题:

I also see this error a dozen or so times throughout the log, though I believe I can fix this just by creating a master key in the database:

06/26/2013 14:25:01,spid116,Unknown,Service Broker needs to access the master key in the database '<Database name>'. Error code:26. The master key has to exist and the service master key encryption is required.

我认为这些错误的数量可能与停留在队列中的对话数量有关.这是我用来订阅查询通知的 C# 代码:

I am thinking the number of these errors may be related to the number of conversations that are staying stuck in the queue. Here is the C# code I am using to subscribe to the query notifications:

private void EstablishSqlConnection(
    String storedProcedureName,
    IEnumerable<SqlParameter> parameters,
    Action sqlQueryOperation,
    String serviceCallName,
    Int32 timeout,
    params MultipleResult[] results)
{
    SqlConnection storeConnection = (SqlConnection) ((EntityConnection) ObjectContext.Connection).StoreConnection;
    try
    {
        using (SqlCommand command = storeConnection.CreateCommand())
        {
            command.Connection = storeConnection;
            storeConnection.Open();

            SqlParameter[] sqlParameters = parameters.ToArray();
            command.CommandText = storedProcedureName;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(sqlParameters);

            if (sqlQueryOperation != null)
            {
                // Register a sql dependency with the SQL query.
                SqlDependency sqlDependency = new SqlDependency(command, null, timeout);
                sqlDependency.OnChange += OnSqlDependencyNotification;
            }

            using (DbDataReader reader = command.ExecuteReader())
            {
                results.ForEach(result => result.MapResults(this, reader));
            }
        }
    }
    finally
    {
        storeConnection.Close();
    }
}

这是我处理通知的方式:

Here is how I handle the notification:

    public static void OnSqlDependencyNotification(object sender, SqlNotificationEventArgs e)
    {
        if (e.Info == SqlNotificationInfo.Invalid)
        {
            // If we failed to register the SqlDependency, log an error
            <Error is loged here...>

            // If we get here, we are not in a valid state to requeue the sqldependency. However,
            // we are on an async thread and should NOT throw an exception. Instead we just return
            // here, as we have already logged the error to the database.
            return;
        }

        // If we are able to find and remove the listener, invoke the query operation to re-run the query.
        <Handle notification here...>
    }

有谁知道什么会导致代理的连接进入这种状态?或者我可以使用哪些工具来试图找出导致这种情况的原因?我目前只有一个 Web 服务器注册其通知，因此我的场景并不太复杂.

Does anyone know what can cause the broker's connections to get in this state? Or what tools I could use to go about trying to figure out what is causing this? I currently only have a single web server that is registering to its notifications, so my scenario is not overly complex.

更新:

好的，所以我从这篇文章中确定了 错误无法找到远程服务......因为它不存在"是由于 SqlDependency 没有正确清理自身.服务结束后，代理仍在尝试向我的应用程序发送通知.所以现在，听起来我只需要找到一种方法来清除我的应用程序在调用 SqlDependency.Start() 之前启动时未正确清理的任何内容，但除了我的原始方法之外，我还没有找到其他方法以上，这使数据库脱机，是不可接受的.有谁知道清理这个吗?

Ok, so I have determined from this post that the error "Cannot find the remote service ... because it does not exist" is due to SqlDependency not cleaning up after itself properly. The broker is still trying to send notifications to my application after the service has ended. So now, it sounds like I just have to find a way to clear out whatever it is not properly cleaning up when my app starts before calling SqlDependency.Start(), but I have not found a way to do this other than my original method above, which takes the database offline and is not acceptable. Does anyone know know to clean this up?

推荐答案

我找到了一种可接受的方法来解决这个问题.首先，我将我的代码从 SqlDependency 迁移出来，现在我使用的是 SqlNotificationRequest.这样做可以防止代理队列和服务在意外时间被创建/销毁.

I have found an acceptable approach to solving this issue. First, I migrated my code away from SqlDependency and I am now using SqlNotificationRequest instead. Doing this prevents Broker Queues and Services from being created/destroyed at unexpected times.

尽管如此，当我的应用程序退出时，仍有一些对话没有被标记为关闭，因为设置通知的原始端点不再存在.因此，每次我的服务器重新初始化我的代码时，我都会清除现有的对话.

Even with this however, when my application exits there are still a few conversations that don't get marked as closed because the original endpoint that setup the notification is no longer there. Therefore, each time my server re-initializes my code I am clearing out existing conversations.

这项调整使我每天拥有的连接数从 1000 多个减少到必须手动杀死它们，一直最多大约 20 个.我强烈推荐使用 SqlNotificationRequest 而不是 SqlDependency.

This adjustment has reduced the number of connections that I have on a daily bases from over 1000 and having to manually kill them, to having a max of about 20 at all times. I highly recommend using SqlNotificationRequest instead of SqlDependency.

这篇关于Service Broker 消息在大约一天后开始挂断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！