Sql-server – Service Broker repeating messages

service-brokersql serversql-server-2016

In our estate we've set up Service Broker to perfom an action:

  1. Based on a schedule a query is executed and its results are sent, via service broker, to the other service on a remote server
  2. Message is received and consumed by activation procedure on target server.

The issue is that for some reason the message is consumed multiple times on target server, despite the fact that it has been sent just once.

I'm fairly new to Service Broker and am seeking for advice/guidance of what could be the exact problem and what could be the cause of it.

The code of the procedure that begins the dialog:

CREATE PROCEDURE [ServiceBroker].[SendMessage]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @Message XML
            , @ConversationHandle UNIQUEIDENTIFIER
            , @Counter INT = 1
            , @Error INT
            , @TRANCOUNT INT = @@TRANCOUNT
            , @SavePoint CHAR(32) = REPLACE(NEWID(), '-', '')
            , @FromService sysname = 'SimpleServiceInitiator_' + DB_NAME();

        SET @Message = (
            SELECT 1 AS Value
            FOR XML PATH('Message'), ELEMENTS XSINIL, TYPE
            );

        BEGIN TRANSACTION;
        SAVE TRANSACTION @SavePoint;

        WHILE 1 = 1
        BEGIN
            SET @ConversationHandle = NULL;

            BEGIN DIALOG @ConversationHandle
            FROM SERVICE @FromService
            TO SERVICE 'SimpleServiceTarget'
            ON CONTRACT SimpleContract
            WITH ENCRYPTION = OFF;

            -- Set an two minute timer on the conversation
            BEGIN CONVERSATION TIMER (@ConversationHandle) TIMEOUT = 120;

            -- Attempt to SEND on the associated conversation
            SEND
                ON CONVERSATION @ConversationHandle
                MESSAGE TYPE SimpleMessage (@Message);

            -- Successful send, just exit the loop
            SET @Error = @@ERROR;
            IF @Error = 0 BREAK;

            SET @Counter += 1;
            IF @Counter > 3
            BEGIN
                -- We failed 3 times in a row, something must be broken
                RAISERROR (N'Failed to SEND on a conversation for more than 3 times. Error %i.', 16, 1, @Error);
                BREAK;
            END;
        END;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() = 1
        BEGIN
            -- Rollback any active or uncommittable transactions before inserting information in the ErrorLog.
            -- We can still save the other activities in the transaction.
            ROLLBACK TRANSACTION @SavePoint;
            COMMIT TRANSACTION;
        END;
        ELSE IF XACT_STATE() = -1 AND @TRANCOUNT = 0
        BEGIN
            -- If the tran is doomed, and the @TRANCOUNT was 0, we have to do a full roll back
            ROLLBACK TRANSACTION;
        END;
        EXECUTE Logs.LogError;
        THROW;
    END CATCH;
END;
GO

And that of the activation SP on target:

CREATE PROCEDURE [ServiceBroker].[ProcessSimpleMessagesFromQueue]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @ConversationHandle UNIQUEIDENTIFIER
            , @MessageType sysname
            , @MessageBody XML
            , @ServiceName sysname
            , @IsRecognised BIT = 0
            , @ErrorCode INT
            , @ErrorDescription NVARCHAR(4000);

        WHILE 1 = 1
        BEGIN
            SET @ConversationHandle = NULL;
            BEGIN TRANSACTION;
            WAITFOR
            (
                RECEIVE TOP (1) @ConversationHandle = conversation_handle
                    , @MessageBody = message_body
                    , @MessageType = message_type_name
                    , @ServiceName = service_name
                -- SELECT *
                FROM dbo.SimpleQueue
            ), TIMEOUT 5000;

            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRANSACTION;
                BREAK;
            END;
            ELSE IF @MessageType = N'EndOfStream'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            BEGIN
                EXECUTE ServiceBroker.ExtractError @MessageBody = @MessageBody
                    , @ErrorCode = @ErrorCode OUTPUT
                    , @ErrorDescription = @ErrorDescription OUTPUT;

                END CONVERSATION @ConversationHandle;

                INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
                VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
            END;
            ELSE IF @MessageType = N'SimpleMessage'
            BEGIN
                SET @IsRecognised = 1;

                /* DO -TSQL MAGIC*/

                END CONVERSATION @ConversationHandle;
            END;
            COMMIT TRANSACTION;
        END;
    END TRY
    BEGIN CATCH
        SET @ErrorCode = ERROR_NUMBER();
        SET @ErrorDescription = ERROR_MESSAGE();

        IF @IsRecognised = 1
        BEGIN
            -- 6a) Commit otherwise end the conversation to get the message off the queue **
            IF @@TRANCOUNT > 0
            BEGIN
                COMMIT TRANSACTION;
            END;
            ELSE
            BEGIN
                SET @ErrorCode = 127;
                SET @ErrorDescription = N'Unable to process message';

                END CONVERSATION @ConversationHandle
                    WITH ERROR = @ErrorCode
                    DESCRIPTION = @ErrorDescription;
            END;
        END;
        ELSE IF XACT_STATE() IN (-1, 1) AND @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;

        INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
        VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
    END CATCH;
END;

This is activation SP on initiator that's supposed to end conversation:

CREATE PROCEDURE [ServiceBroker].[ProcessSimpleMessagesFromQueue_Initiator]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @ConversationHandle UNIQUEIDENTIFIER
            , @MessageType sysname
            , @MessageBody XML
            , @ServiceName sysname
            , @ErrorCode INT
            , @ErrorDescription NVARCHAR(4000);

        WHILE 1 = 1
        BEGIN
            BEGIN TRANSACTION;
            WAITFOR
            (
                RECEIVE TOP (1) @ConversationHandle = conversation_handle
                    , @MessageBody = message_body
                    , @MessageType = message_type_name
                    , @ServiceName = service_name
                FROM dbo.SimpleQueue
            ), TIMEOUT 5000;

            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRANSACTION;
                BREAK;
            END;
            ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            BEGIN
                END CONVERSATION @ConversationHandle;
            END;
            ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            BEGIN
                EXECUTE ServiceBroker.ExtractError @MessageBody = @MessageBody
                    , @ErrorCode = @ErrorCode OUTPUT
                    , @ErrorDescription = @ErrorDescription OUTPUT;

                END CONVERSATION @ConversationHandle;

                INSERT INTO Logs.ConversationError (ServiceName, ConversationHandle, MessageType, MessageBody, ErrorCode, ErrorDescription)
                VALUES (@ServiceName, @ConversationHandle, @MessageType, @MessageBody, @ErrorCode, @ErrorDescription);
            END;
            COMMIT TRANSACTION;
        END;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        SET @ErrorCode = ERROR_NUMBER();
        SET @ErrorDescription = ERROR_MESSAGE();

        END CONVERSATION @ConversationHandle
            WITH ERROR = @ErrorCode
            DESCRIPTION = @ErrorDescription;

        EXECUTE Logs.LogError;
        THROW;
    END CATCH;
END;
GO

Best Answer

Dan Guzman gave me some good tips and after further research I've found out that my routes were configured incorrectly.