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 `

Check the SQL Server error log for messages related to activation errors. — Dan Guzman, Aug 10 '19 at 00:19
Thanks @DanGuzman. Nothing valuable in error log. By the way, my activation stored proc is being activated, on all other occasions (outbound error message, custom ping implementation). I gave a look at some thread from Remuses' dealing with activation troubleshooting with no luck. I can provide with additional metrics regarding my "guilty" queue. — tivivi, Aug 12 '19 at 13:30
Forgot to tell that I also ran xevent monitoring session, scoping to queue activation event, and no event was hooked for EndDialog case, while everything runs fine for other cases. — tivivi, Aug 12 '19 at 13:54

score 0 · Accepted Answer · answered Aug 12 '19 at 13:52

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.

Hello @david-browne-microsoft. Thanks for the valuable feedback. Indeed, there is a leak on my activation procedure, related to the process of multiples messages back in-a-row. I'll fix this. Nevertheless, this is not the reason I will mark this thread as resolved for. I noticed I didn't realize so far that I was unexpectedly doing fire-and-forget messaging :( Ending conversation must be initiated either by target or by initiator **when responding to an incoming error notification from target**. Never from initiator in the first place, unless activation will never trigger. — tivivi, Aug 16 '19 at 09:18

Initiator never activated when acknowledged of EndDialog

1 Answers1