Service broker messages start to hang after about a day

Question

I have an application that uses Service Broker - this is SQL 2008. About once a day, database performance starts to decline noticeably, and I decided it was because of 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 

Then, performance returns to normal until the next day. I also noticed that at low performance, executing the following query returns a large number (about 1000 at present) of conversations that are stuck in the STARTED_OUTBOUND state:

 SELECT * FROM sys.conversation_endpoints 

In addition, the following queries do not return any records in them:

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

Performance seems to be in order where there are many elements returned by this query. The only time that problems occur is when there are connections that STARTED_OUTBOUND remain in this state.

The only configuration I made for Service Broker on my instance of SQL Server 2008 was as follows:

 ALTER DATABASE RegencyEnterprise SET ENABLE_BROKER 

Digging into the SQL error log, I found this entry 1000 times:

 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 for about a dozen or so in the entire log, although I believe that I can fix it 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. 

I think that the number of these errors may be related to the number of chains that remain in the queue. Here is the C # code that I use to sign up for request 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(); } } 

This 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...> } 

Does anyone know what can cause broker connections to enter this state? Or what tools could I use to try to figure out what causes this? Currently, I only have one web server that logs in its notifications, so my scenario is not too complicated.

UPDATE:

Well, that’s why I determined from this post that the error "Cannot find the remote service ... because it does not exist" is due to SqlDependency not clearing itself properly. The broker is still trying to send notifications to my application after the end of the service. So now it seems like I just need to find a way to clean everything that it doesn’t clean properly when my application starts before calling SqlDependency.Start (), but I did not find a way to do it other than my original method above, which disables database and not suitable. Does anyone know to know to clear this?

Answer 1

I found an acceptable approach to solving this problem. First, I migrated the code from SqlDependency, and now I use SqlNotificationRequest. This prevents the creation / destruction of Broker Queues and Services at unexpected times.

Even so, however, when my application exits, there are a few more conversations that do not become flagged as closed, because the original endpoint that sets up the notification no longer exists. Therefore, every time my server reinitializes my code, I clear existing conversations.

This setting has reduced the number of connections that I have on daily bases from more than 1000, and they need to be manually killed to have a maximum of about 20 at any time. I highly recommend using SqlNotificationRequest instead of SqlDependency.

Answer 2

I found a way to clear conversations that are stuck. I retrieve all the generated SqlDependency queues that still exist, and iterate over conversations that do not belong to any of them and end these conversations. Below is the code:

 SET NOCOUNT OFF; DECLARE @handle UniqueIdentifier DECLARE @count INT = 0 -- Retrieve orphaned conversation handles that belong to auto-generated SqlDependency queues and iterate over each of them DECLARE handleCursor CURSOR FOR SELECT [conversation_handle] FROM sys.conversation_endpoints WITH(NOLOCK) WHERE far_service COLLATE SQL_Latin1_General_CP1_CI_AS like 'SqlQueryNotificationService-%' COLLATE SQL_Latin1_General_CP1_CI_AS AND far_service COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS FROM sys.service_queues) DECLARE @Rows INT SELECT @Rows = COUNT(*) FROM sys.conversation_endpoints WITH(NOLOCK) WHERE far_service COLLATE SQL_Latin1_General_CP1_CI_AS like 'SqlQueryNotificationService-%' COLLATE SQL_Latin1_General_CP1_CI_AS AND far_service COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS FROM sys.service_queues) WHILE @ROWS>0 BEGIN OPEN handleCursor FETCH NEXT FROM handleCursor INTO @handle BEGIN TRANSACTION WHILE @@FETCH_STATUS = 0 BEGIN -- End the conversation and clean up any remaining references to it END CONVERSATION @handle WITH CLEANUP -- Move to the next item FETCH NEXT FROM handleCursor INTO @handle SET @count= @count+1 END COMMIT TRANSACTION print @count CLOSE handleCursor; IF @count > 100000 BEGIN BREAK; END SELECT @Rows = COUNT(*) FROM sys.conversation_endpoints WITH(NOLOCK) WHERE far_service COLLATE SQL_Latin1_General_CP1_CI_AS like 'SqlQueryNotificationService-%' COLLATE SQL_Latin1_General_CP1_CI_AS AND far_service COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS FROM sys.service_queues) END DEALLOCATE handleCursor; 

Answer 3

Started Outbound means that SQL Server has processed the BEGIN CONVERSATION for this conversation, but the messages have not yet been sent. '(from books on the Internet) It looks like you are creating conversations that are not in use, so they never close.

Not quite sure why this will lead to poor performance.