SQL Server service broker end conversation creates new entries on initiator queue

We are using SQL Service Broker, using two services for initiator and target, then we have a C# service listening and receiving messages from queue.

On SQL Side the setup is this:

    CREATE MESSAGE TYPE [http://mycompany.com/MyEventRequestMessage] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [http://mycompany.com/MyEventResponseMessage] VALIDATION = WELL_FORMED_XML
GO

CREATE CONTRACT [http://mycompany.com/MyMessageContract] ([http://mycompany.com/MyEventRequestMessage] SENT BY INITIATOR,
[http://mycompany.com/MyEventResponseMessage] SENT BY TARGET)
GO

CREATE QUEUE [dbo].[MyEventInitiatorQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY] 
GO
CREATE QUEUE [dbo].[MyEventTargetQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY] 
GO

CREATE SERVICE [MyEventInitiatorService]  ON QUEUE [dbo].[MyEventInitiatorQueue] ([http://mycompany.com/MyMessageContract])
GO
CREATE SERVICE [MyEventTargetService]  ON QUEUE [dbo].[MyEventTargetQueue] ([http://mycompany.com/MyMessageContract])
GO

Then if I insert a message:

DECLARE @MessageType NVARCHAR(128) = 'http://mycompany.com/MyEventRequestMessage'
DECLARE @MessageBody NVARCHAR(128) = 'loremipsum'
DECLARE @FromService NVARCHAR(128) = 'MyEventInitiatorService'
DECLARE @ToService NVARCHAR(128) = 'MyEventTargetService'
DECLARE @Contract NVARCHAR(128) = 'http://mycompany.com/MyMessageContract'

DECLARE @conversation_handle UNIQUEIDENTIFIER;
 
    BEGIN DIALOG CONVERSATION @conversation_handle
        FROM SERVICE @FromService
        TO SERVICE @ToService
        ON CONTRACT @Contract
        WITH ENCRYPTION = OFF;
 
    SEND ON CONVERSATION @conversation_handle   
        MESSAGE TYPE @MessageType(@MessageBody);

It arrives the target queue as expected.

Then on the service we receive the message rom target queue and perform END CONVERSATION.

The message disapears from target queue, but then a new message is inserted on initiator queue, of type http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog.

Even though our service is prepared to end these conversations, it is listening on the target queue.

How can we clear these end dialog messages? Should we listen also on the initiator queue these kind of messages, in order to end the conversation, or should we do anything else?

We are afraid if this causes any side efect on the database or on the server if no action is made.

Thanks.

2 answers

  • answered 2021-09-27 17:19 Dan Guzman

    How can we clear these end dialog messages? Should we listen also on the initiator queue these kind of messages, in order to end the conversation, or should we do anything else?

    Yes, you also need a service (technically any SQL process) that listens for initiator queue messages to end that side of the conversation when an EndDialog or Error is message received. Consider logging any unexpected message types (i.e. other than EndDialog).

    You'll eventually fill up data database with unneeded initiator queue messages if no action is taken,

  • answered 2021-09-28 08:34 user729400

    I think we found a solution that matches Dan's comment:

    CREATE PROCEDURE spHandleInitiatorQueueMessages
    
    AS
    
    DECLARE @dh UNIQUEIDENTIFIER;
    
    DECLARE @message_type SYSNAME;
    
    DECLARE @message_body NVARCHAR(4000);
    
    BEGIN TRANSACTION;
    
    WAITFOR (
    
          RECEIVE @dh = [conversation_handle],
    
                @message_type = [message_type_name],
    
                @message_body = CAST([message_body] AS NVARCHAR(4000))
    
          FROM MyInitiatorQueue), TIMEOUT 1000;
    
    WHILE @dh IS NOT NULL
    
    BEGIN
    
          IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    
              BEGIN
    
                    RAISERROR (N'Received error %s from service [Target]', 10, 1, @message_body) WITH LOG;
    
              END
    
          END CONVERSATION @dh;
    
          COMMIT;
    
          SELECT @dh = NULL;
    
          BEGIN TRANSACTION;
    
          WAITFOR (
    
                RECEIVE @dh = [conversation_handle],
    
                      @message_type = [message_type_name],
    
                      @message_body = CAST([message_body] AS NVARCHAR(4000))
    
                FROM MyInitiatorQueue), TIMEOUT 1000;
    
    END
    
    COMMIT;
    
    GO
    
     
    
    ALTER QUEUE MyInitiatorQueue
    
          WITH ACTIVATION (
    
                STATUS = ON,
    
                MAX_QUEUE_READERS = 1,
    
                PROCEDURE_NAME = spHandleInitiatorQueueMessages,
    
                EXECUTE AS OWNER);
    
    GO
    

    Messages began to actually end

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum