Service Broker activation procedure firing when queue is empty

Question

I'm sending a single message on a queue with an activation procedure that is intended to run continuously. The procedure receives the message, ends the conversation and continues to execute as expected. Meanwhile, new instances are being activated repeatedly and timing out on the receive (which makes sense, because the queue is empty) even though no additional messages are sent. What would cause the procedure to be activated without a send, and how do I prevent it? Setting max_queue_readers to 1 is not an option, as I intend to send multiple messages once I get this issue resolved.

Answer 1

This script will demonstrate the issue. It'll run for 1+ minute to recreate the bad procedure activations. I've recreated it in versions 15.0.2000.5 and 15.0.2095.3. There is a single SEND, and the procedure is activated every 15 seconds.

create database bugaboo
go
alter database bugaboo set enable_broker
go
use bugaboo
go
create  table MessageLog (
        ID                  int identity not null ,
        MessageTime         datetime not null default getdate(),
        SPID                int not null,
        ProcName            sysname null,
        Handle              uniqueidentifier null,
        Action              varchar(8) null,
        MessageType         sysname null,
        Message             varbinary(max) null,
        constraint          PKMessageLog primary key (ID))
go
create  procedure spKeepRunning
as
        declare @Handle     uniqueidentifier,
                @Type       sysname,
                @Message    varbinary(max),
                @rowcount   int;
        waitfor(receive top (1)
                @Handle     = conversation_handle,
                @Type       = message_type_name,
                @Message    = message_body
                from        qBugaboo), timeout 7000;
        select  @rowcount = @@rowcount
                
        insert  into MessageLog (SPID, ProcName, Handle, Action, MessageType, Message) values (
                @@spid, 'spKeepRunning', @Handle, 'RECEIVE', @Type, @Message)
        if      @rowcount = 0
                return
        end     conversation @Handle
        select  @rowcount = 0
        while   @rowcount = 0
        begin
                select  @rowcount = count(*)
                        from    MessageLog
                        where   Action = 'STOP'
                if      @rowcount = 0
                        waitfor delay '00:00:08'
        end
go
create  procedure spResponse 
as
        declare @Handle     uniqueidentifier,
                @Type       sysname,
                @Message    varbinary(max),
                @rowcount   int;
        waitfor(receive top (1)
                @Handle     = conversation_handle,
                @Type       = message_type_name,
                @Message    = message_body
                from        qResponse), timeout 7000;
        insert  into MessageLog (SPID, ProcName, Handle, Action, MessageType, Message) values (
                @@spid, 'spResponse', @Handle, 'RECEIVE', @Type, @Message)
        if      @Handle is not null
                end     conversation @Handle
go
create  message type mtBugaboo validation = none;
create  contract coBugaboo (mtBugaboo sent by any);
create  queue qBugaboo with 
        status = on, retention = off, 
        activation (status = on, procedure_name = spKeepRunning, max_queue_readers = 8, execute as 'dbo');
create  service svBugaboo on queue qBugaboo (coBugaboo);
create  queue qResponse with 
        status = on, retention = off,
        activation (status = on, procedure_name = spResponse, max_queue_readers = 8, execute as 'dbo');
create  service svResponse on queue qResponse (coBugaboo);

declare @Handle     uniqueidentifier,
        @Message    varbinary(max),
        @dt         datetime
begin   dialog @Handle
        from service    svResponse
        to service      'svBugaboo'
        on contract     coBugaboo
        with encryption = off;
select  @Message = convert(varbinary(max), 'bugaboo');
send    on conversation @Handle message type mtBugaboo (@Message)
insert  into MessageLog (SPID, ProcName, Handle, Action, MessageType, Message) values (
        @@spid, 'n/a', @Handle, 'SEND', 'mtBugaboo', @Message)
select  @dt = dateadd(minute, 1, getdate())
while   getdate() < @dt
begin
        waitfor delay '00:00:13'
end
insert  into MessageLog (SPID, ProcName, Handle, Action, MessageType, Message) values (
        @@spid, 'n/a', @Handle, 'STOP', 'mtBugaboo', @Message)
waitfor delay '00:00:13'
select  datediff(millisecond, lag(MessageTime) over (partition by ProcName order by MessageTime), MessageTime) Dur, * 
        from MessageLog
        order by MessageTime