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

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.

CodePudding user response：

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,

CodePudding user response：

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