Initiator never activated when acknowledged of EndDialog

Question

I wish to comply with Remus Rusanu's dialog recycling technique in my SSB implementation. I wrote some activation procedure for initiator queue, in order to hook EndDialog message back from target and clean the Dialog table from the closed conversation handle.

Nevertheless, though EndDialog ack properly reaches initiator side, no activation is triggered, so my message handler cannot operate and clean the place.

CREATE PROCEDURE fdwh.ProcessResponse
AS
BEGIN
    DECLARE @dlgId UNIQUEIDENTIFIER;
    DECLARE @msgTypeName SYSNAME;
    DECLARE @msgBody VARBINARY(MAX);
    DECLARE @payloadHistoryId   INT;

    BEGIN TRY
        BEGIN TRANSACTION

        WAITFOR(
            RECEIVE TOP(1)
                @dlgId = [conversation_handle],
                @msgTypeName = message_type_name,
                @msgBody = message_body
            FROM [fdwh].[SenderQueue]), TIMEOUT 10;

        -- Message is regular end of conversation, terminate it
        IF (@msgTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
        BEGIN
            END CONVERSATION @dlgId;

            DELETE FROM DWH_BOARD.dbo.Dialog
            WHERE (DbId = DB_ID()) AND
                  (DialogId = @dlgId);
        END

        -- Message is error, extracts and logs number and description
        IF (@msgTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
        BEGIN
[...]

I expect queue activation to be triggered and http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message to be processed as well, but it's not. Isn't EndDialog ACK a regular message?

Please find below a Profiler trace screenshot that is self explaining: .

Example is pure local (single instance/two DBs).

Thanks,

Update A few more metrics for failing queue: `SELECT que.[name], que.is_activation_enabled, que.is_receive_enabled, que.is_poison_message_handling_enabled, que.activation_procedure, que.max_readers, [execute_as] = (SELECT pri.[name] FROM sys.database_principals pri WHERE pri.principal_id = que.execute_as_principal_id) FROM sys.service_queues que WHERE que.[name] = 'SenderQueue'; GO

SELECT conversation_handle, to_service_name, message_type_name, is_conversation_error, is_end_of_dialog, enqueue_time, transmission_status FROM sys.transmission_queue; GO

SELECT [name], is_broker_enabled, log_reuse_wait_desc FROM sys.databases WHERE database_id = 8; GO

EXEC sp_spaceused 'fdwh.SenderQueue'; GO

SELECT * FROM sys.dm_broker_activated_tasks WHERE database_id=8; GO

SELECT [state], last_activated_time, tasks_waiting FROM sys.dm_broker_queue_monitors WHERE database_id = 8; GO `

Answer 1

Activation occurs only when new messages arrive.

"When STATUS = ON, the queue starts the stored procedure specified with PROCEDURE_NAME when the number of procedures currently running is less than MAX_QUEUE_READERS and when messages arrive on the queue faster than the stored procedures receive messages."

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-queue-transact-sql?view=sql-server-2017

The activation procedure is expected to continue consuming messages until the queue is empty, and remains empty for the duration of its WATFOR ... RECEIVE.

Your activation procedure is missing the loop. It's RECEIVING a single message and exiting. So every time a new message arrives a single old message is consumed. This may appear to work for a while, but if you ever have get a backlog of messages, you'll never catch up.