0

Let me rephrase my previous question because issue reproduction is much simpler than I thought.

When I close Service Broker dialogs sometimes I get a message The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.

Repro. There are two mssql instances ( hosted on different Azure VMs ) with aliases servicebrokersender and servicebrokertarget.

-- on both sides
create master key
create certificate
create service broker endpoint
create certificate from the peer
grant connect to endpoint
-- servicebrokersender
use master
go
-- create sender DBs
declare @i int = 1
      , @cmd varchar(max);

while ( @i <= 10 )
begin

    set @cmd = '
    create database serviceBrokertest_' + cast( @i as varchar ) + '
    alter database serviceBrokertest_' + cast( @i as varchar ) + ' set enable_broker
    '
    exec ( @cmd );

    set @i += 1;
end
go
-- create service broker objects in sender DBs
declare @cmd nvarchar(4000)
      , @name sysname
declare cur cursor  local fast_forward for
select name from sys.databases
  where name like 'serviceBrokertest_%' 
open cur
fetch next from cur into @name
while ( @@fetch_status = 0 )
begin
   select @cmd = '  use [' + @name + ']
                    create message type [test_message] validation = none;
                    create contract [test_contract] ( [test_message] sent by any );
                    create queue dbo.sender_queue_outbound with status = on, retention = off, poison_message_handling ( status = off ) on [default];
                    create service [servicebrokersender_' + @name + '_outbound] on queue sender_queue_outbound( test_contract );
                 '
  exec ( @cmd )
  fetch next from cur into @name
end
close cur
deallocate cur
go
-- servicebrokertarget
-- create target DB and Service Broker objects
create database serviceBrokertestTargetDb
alter database serviceBrokertestTargetDb set enable_broker

use serviceBrokertestTargetDb
go
create message type [test_message] validation = none;
create contract [test_contract] ( [test_message] sent by any );
create queue dbo.target_queue_inbound with status = on, retention = off, poison_message_handling ( status = off ) on [default];
create service serviceBrokertestTargetDb_inbound on queue target_queue_inbound( test_contract );
go
select service_broker_guid from sys.databases where name = db_name() -- 5405D327-1DD9-4E22-BFE3-23008129E971
go

Сreating routes

-- servicebrokersender
declare @cmd nvarchar(4000)
      , @name sysname
declare cur cursor  local fast_forward for
select name from sys.databases
  where name like 'serviceBrokertest_%' 
open cur
fetch next from cur into @name
while ( @@fetch_status = 0 )
begin
   select @cmd = '  use [' + @name + ']
                    create route [route_to_serviceBrokertestTargetDb_inbound] with service_name = ''serviceBrokertestTargetDb_inbound'', address = ''TCP://servicebrokertarget:4022'', broker_instance = ''5405D327-1DD9-4E22-BFE3-23008129E971'';
                 '
  exec ( @cmd )
  fetch next from cur into @name
end
close cur
deallocate cur
go
declare @cmd nvarchar(4000)
      , @name sysname
drop table if exists #t;
create table #t( s sysname, d uniqueidentifier )
declare cur cursor  local fast_forward for
select name from sys.databases
  where name like 'serviceBrokertest_%' 
open cur
fetch next from cur into @name
while ( @@fetch_status = 0 )
begin
   select @cmd = '  use [' + @name + ']
                    select ( select name from sys.services where service_id > 65535 ) as s, ( select service_broker_guid from sys.databases where name = db_name() ) as d 
                 '
  insert into #t
    exec ( @cmd )
  fetch next from cur into @name
end
close cur
deallocate cur
go
select 'route_to' + s, * from #t
go
-- servicebrokertarget
create route [route_toservicebrokersender_serviceBrokertest_1_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_1_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '8D9F2117-76D6-4000-9902-C5E509AFC15B';
create route [route_toservicebrokersender_serviceBrokertest_10_outbound]   with service_name = 'servicebrokersender_serviceBrokertest_10_outbound'  , address = 'TCP://servicebrokersender:4022', broker_instance = 'D1C6C18D-97DD-4D40-84EC-AFA065B97846';
create route [route_toservicebrokersender_serviceBrokertest_2_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_2_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '48FED7D4-0116-4146-9FD1-0BF2932D87E1';
create route [route_toservicebrokersender_serviceBrokertest_3_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_3_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '07DD4094-07A6-4A56-93DB-9F9DD165D1EB';
create route [route_toservicebrokersender_serviceBrokertest_4_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_4_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = 'B199DE3A-F3EE-4535-A086-5AD3A24C650D';
create route [route_toservicebrokersender_serviceBrokertest_5_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_5_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '925F764F-F0C8-4D7E-B09D-3B2EF6D61A83';
create route [route_toservicebrokersender_serviceBrokertest_6_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_6_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '887BBBE3-BC72-4D89-A5B0-43231C604596';
create route [route_toservicebrokersender_serviceBrokertest_7_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_7_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '18231D38-7695-4E1B-B017-93A4C25B5679';
create route [route_toservicebrokersender_serviceBrokertest_8_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_8_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '85CFE9EF-A33E-4173-A17C-6C7DB26B2619';
create route [route_toservicebrokersender_serviceBrokertest_9_outbound]    with service_name = 'servicebrokersender_serviceBrokertest_9_outbound'   , address = 'TCP://servicebrokersender:4022', broker_instance = '6860CDAC-F0AB-4634-BA0E-4FA79C04B539';
go
-- End dialogs activation procedure on servicebrokersender:
declare @cmd nvarchar(4000)
      , @name sysname
declare cur cursor  local fast_forward for
select name from sys.databases
  where name like 'serviceBrokertest_%' 
open cur
fetch next from cur into @name
while ( @@fetch_status = 0 )
begin
   select @cmd = '  use [' + @name + ']
go
create or alter procedure dbo.usp_sender_queue_outbound_activation
as
begin

    set xact_abort on;

    declare @handle             uniqueidentifier
          , @messageTypeName    sysname
          , @messageBody        varbinary(max);

    set transaction isolation level read committed;

    begin transaction;

    receive top(1) @handle = conversation_handle
                 , @messageTypeName = message_type_name
                 , @messageBody = message_body
        from [sender_queue_outbound];

    if @handle is not null
    begin

        if @messageTypeName = N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog''
        begin

            end conversation @handle;

        end        
        else 
        begin
            -- just in case
            raiserror(N''Service Broker: Description:"%s"'',0, 0, @messageTypeName) with log;

        end;
    end

    commit;

end
go
alter queue [sender_queue_outbound] with activation (
  status = on
, max_queue_readers = 1
, procedure_name = dbo.usp_sender_queue_outbound_activation
, execute as ''dbo'');
go
'
    print @cmd -- and exec
  fetch next from cur into @name
end
close cur
deallocate cur
go
-- Permissions on servicebrokertarget:
grant send on service::[serviceBrokertestTargetDb_inbound] to public;
go

So, Service Broker infrastructure is ready, let's open 3000 dialogs from every sender database:

declare @cmd nvarchar(4000)
      , @name sysname
declare cur cursor  local fast_forward for
select name from sys.databases
  where name like 'serviceBrokertest_%' 
open cur
fetch next from cur into @name
while ( @@fetch_status = 0 )
begin
   select @cmd = '  use [' + @name + ']
                    declare @handle uniqueidentifier;
                    begin dialog conversation @handle 
                        from service [servicebrokersender_' + @name + '_outbound]
                        to   service ''serviceBrokertestTargetDb_inbound''
                            on contract [test_contract]
                        with encryption = off;
                    ;send on conversation @handle 
                      message type [test_message]( ''TestServiceBrokerMessage'' );
                 '
    declare @i int = 0;
    while ( @i < 3000 )
    begin
        exec ( @cmd )
        set @i += 1;
    end

  fetch next from cur into @name
end
close cur
deallocate cur
go

servicebrokertarget:

-- Check the result
select count(*) from sys.conversation_endpoints where [state] = 'CO' -- 30000 rows
select count(*) from dbo.target_queue_inbound -- 30000 rows
go

And close dialogs from target side. It's a imitation of real workload servicebrokertarget:

while exists ( select * from sys.conversation_endpoints where [state] = 'CO' )
begin
    declare @cmd nvarchar(max) = ''
    select top 1000 @cmd += ';end conversation ' + '''' + cast( conversation_handle as nvarchar(128 ) ) +''' ' from sys.conversation_endpoints where [state] = 'CO'
    exec ( @cmd );
end

Before dialogs closing I've set up xEvent session with broker events on both sides.

And what I see on servicebrokersender ( session filtered by broker_message_undeliverable event ): enter image description here

There were errors in some chunk of dialogs during end conversation process on the sender side. On the screenshot the amount of dialogs ended with the error is 3521 but it could be a random number. After that looks like conversations was closed eventually and I don't see any conversations ( using sys.conversation_endpoints ) on the sender side and all conversations on target side are in CLOSED state.

Let's consider some of the errored conversations deeply.

First one:

servicebrokersender: enter image description here servicebrokertarget: enter image description here

Second one:

servicebrokersender: enter image description here servicebrokertarget: enter image description here

So, I see that the situation could be related to time lag between end conversation on the sender side and acknowledgment on the target side. But I don't know for sure.

So, my questions:

  1. Why it happens?
  2. Should I care about this? I mean maybe the errors could lead to resource leak or smth like that

UPDATE

According to David remarks:

  1. I've added logging in senders activation stored procedures
-- End dialogs activation procedure on servicebrokersender:
declare @cmd nvarchar(4000)
      , @name sysname
declare cur cursor  local fast_forward for
select name from sys.databases
  where name like 'serviceBrokertest_%' 
open cur
fetch next from cur into @name
while ( @@fetch_status = 0 )
begin
   select @cmd = '  use [' + @name + ']
go
drop table if exists dbo.activation_proc_logging
go
create table dbo.activation_proc_logging (
  get_date              datetime
, conversation_handle   uniqueidentifier
, message_type_name     sysname
, message_body          varbinary(max)
, [status]              bit
, [status_date]         datetime
)
go
create or alter procedure dbo.usp_sender_queue_outbound_activation
as
begin

    set xact_abort on;

    declare @handle             uniqueidentifier
          , @messageTypeName    sysname
          , @messageBody        varbinary(max);

    set transaction isolation level read committed;

    begin transaction;

    receive top(1) @handle = conversation_handle
                 , @messageTypeName = message_type_name
                 , @messageBody = message_body
        from [sender_queue_outbound];

    insert into dbo.activation_proc_logging ( get_date, conversation_handle, message_type_name, message_body, [status], [status_date] )
      select getdate(), @handle, @messageTypeName, @messageBody, 0, getdate()

    if @handle is not null
    begin

        if @messageTypeName = N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog''
        begin

            end conversation @handle;
            update dbo.activation_proc_logging
              set [status] = 1, [status_date] = getdate()
              where conversation_handle = @handle

        end        
        else 
        begin
            -- just in case
            raiserror(N''Service Broker: Description:"%s"'',0, 0, @messageTypeName) with log;

        end;
    end

    commit;

end
go
alter queue [sender_queue_outbound] with activation (
  status = on
, max_queue_readers = 1
, procedure_name = dbo.usp_sender_queue_outbound_activation
, execute as ''dbo'');
go
'
    print @cmd -- and exec
  fetch next from cur into @name
end
close cur
deallocate cur
go
  1. I've performed conversations ending one by one
set nocount on;
select 'end conversation ''' + cast( conversation_handle as nvarchar(128) ) + ''';'  from sys.conversation_endpoints where [state] = 'CO'
-- 30000 rows

Result the same: I have this kind of errors. I even got a new error message: "This message could not be delivered because the 'receive end conversation' action cannot be performed in the 'DISCONNECTED_INBOUND' state." related to some conversations

servicebrokersender: enter image description here

Unfortunatelly, xEvents session was stopped for servicebrokertarget:

What about dbo.activation_proc_logging tables ( status = 1 means that conversation was ended ):

-- sum is 30000
select sum( cnt ) from (
select count(*) as cnt from serviceBrokertest_1.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_2.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_3.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_4.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_5.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_6.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_7.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_8.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_9.dbo.activation_proc_logging  where status = 1 union all
select count(*) as cnt from serviceBrokertest_10.dbo.activation_proc_logging where status = 1 ) z

Every conversation was ended.


UPDATE_2

Actually the amount of rows in every senders logging table

select count(*) from dbo.activation_proc_logging 

equals 3001, not 3000. Because there was another one message with conversation_handle is null, message_type_name is null, message_body is null. I don't know what this is

2
  • 1
    Can you tweak the repro so you are sure to send exactly 3000 END CONVERSATION's. What you have should do that, but if there is a delay between END CONVERSATION and the conversation switching to DI in sys.conversation_endpoints that could cause your current code to attempt to end the same conversation twice. And I would also update your sender's activation procedure to record each conversation handle it ends to show that it's not ending the conversation. Mar 4 at 15:24
  • I've updated the topic
    – Pavel Zv
    Mar 4 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.