SQL Broker sys.conversation_groups not emptying after conversation is ended

Question

I have SQL Server 2016 Broker enabled on a database. I'm currently testing and I see that the sys.conversation_groups table doesn't cleanup even after I end the conversations. Should I worry about that? I'm having a hard time understanding the role of this table in all of this.

Please note that I'm sending message FROM and TO my Request queue. Is it a bad practice? I don't need an answer. I read the messages in my C# application then end the conversation.

Here is my setup :

CREATE MESSAGE TYPE SomeMessageType VALIDATION=NONE;
CREATE MESSAGE TYPE SomeReplyType VALIDATION=NONE; 

CREATE CONTRACT MyMessageContract
(
 SomeMessageType SENT BY INITIATOR 
,SomeReplyType SENT BY TARGET 
);

CREATE QUEUE MyBrokerRequestsQueue

CREATE SERVICE BrokerRequestsServices
ON QUEUE MyBrokerRequestsQueue (MyMessageContract); 

Then, I send messages this way (still on my test server, will be sent by a trigger after the tests are done) :

declare @count int;
set @count = 1;

declare @msg nvarchar(max);
set @msg = 'This is a test ';

while (@count <= 100)
begin
    DECLARE @handle UNIQUEIDENTIFIER;

    BEGIN DIALOG @handle
      FROM SERVICE BrokerRequestsServices
      TO SERVICE 'BrokerRequestsServices'
      ON CONTRACT MyMessageContract
      WITH ENCRYPTION = ON;

      set @msg = 'This is a test ' + convert(nvarchar(3), @count);

      SEND ON CONVERSATION @handle MESSAGE TYPE SomeMessageType  ( @msg );

      set @count = @count + 1
end

The C# program reads the queue this way :

string SQL = string.Format(@"
        waitfor( 
            RECEIVE top (@count) conversation_handle,service_name,message_type_name,message_body,message_sequence_number 
            FROM [{0}] 
                ), timeout @timeout", queueName);
  SqlCommand cmd = new SqlCommand(SQL, con);

  SqlParameter pCount = cmd.Parameters.Add("@count", SqlDbType.Int);
  pCount.Value = maxMessages;

  SqlParameter pTimeout = cmd.Parameters.Add("@timeout", SqlDbType.Int);

  if (timeout == TimeSpan.MaxValue)
  {
    pTimeout.Value = -1;
  }
  else
  {
    pTimeout.Value = (int)timeout.TotalMilliseconds;
  }

  cmd.CommandTimeout = 0; //honor the RECIEVE timeout, whatever it is.


  return cmd.ExecuteReader();

And then when it gets a message, reads the conversation handle and ends it.

END CONVERSATION @ConversationHandle;

Am I doing something wrong here?

Answer 1

Ok so I think my problems are caused by the "fire and forget" pattern. But in the case of my application, this is really the pattern that we need. (We don't handle any errors.) Furthermore, we have a service that threats those messages and if the service was stopped, it reinitialize itself and doesn't need the messages that were generated while it was down.

So in order to get rid of the problem of the conversations staying in Conversing state, I modified the procedure that sends the message to add a lifetime of 2 minutes for the messages.

BEGIN DIALOG @handle
FROM SERVICE BrokerRequestsServices
TO SERVICE 'BrokerRequestsServices'
ON CONTRACT MyMessageContract
WITH ENCRYPTION = ON, LIFETIME = 120;

That way, if the service is up, it will read the message and end the conversation, and even if the conversation stays in the "Conversing" state, it will disappear after two minutes. And if the service is down, it's not gonna read old messages after it gets back up.