about 'End Conversation'

Jul 8, 2016 at 6:39 AM
Edited Jul 8, 2016 at 10:03 AM
I tested too volume insert, and delete tables, found conversation_endpoints table, there are a lot of 'state' as 'DI' conversation. And they will not be cleaned up. Finally, lead to 'dependency.Stop ()' has failed. The reason is that 'DropDatabaseObjects ()' timed out. I wrote a stored procedure, put it in sqlserver agent, you can clean up these conversations have been completed.
if exists(select name from sys.objects where name = 'usp_EndConversation')
    drop procedure usp_EndConversation
go
create procedure usp_EndConversation
    @serviceName nvarchar(50)
as
begin
    begin transaction
    begin try
        declare @i int
        declare @count int
        create table #ConversationHandle
        (
            conversation_handle nvarchar(100) not null
        )
        
        if(@serviceName = 'TableDependency_Bank')
        begin
            insert into #ConversationHandle
            select conversation_handle FROM sys.conversation_endpoints
            where far_service = 'TableDependency_Bank' and state = 'DI' and conversation_handle not in (
                select conversation_handle from TableDependency_Bank where service_name = 'TableDependency_Bank' group by conversation_handle)
        end
 -- Order other service

                
        set @i = 1
        select @count = COUNT(*) from #ConversationHandle
        while(@i <= @count)
        begin
            declare @handle nvarchar(100)
            
            select @handle = conversation_handle
            from (select ROW_NUMBER() over(order by conversation_handle) as rn, conversation_handle
                from #ConversationHandle) as data
            where data.rn = @i
            
            end conversation @handle with cleanup
            set @i = @i + 1
        end
                
        select 'commit'
        commit transaction
    end try
    begin catch
        select 'rollback'
        rollback transaction
    end catch
end
go
This stored procedure 'TableDependency_Bank' is the name of my custom. Generated on:
 List<string> updateOf = new List<string>() { "ID", "Abbreviation", "FullName", "ServiceHotline", "Description" };
            dependency = new SqlTableDependency<_Bank>(connectionString, "Bank",  updateOf, false, "TableDependency_Bank");
Finally, thank Table Dependency team。
Jul 9, 2016 at 7:09 PM
Hello.

I am a bit confused about your "load" test, especially for this:

I tested too volume insert, and delete tables, found conversation_endpoints table, there are a lot of 'state' as 'DI' conversation

Tables monitored from SqlTableDependency, should not be deleted before calling .Stop().

Stop() executes a DB clean up, removing all objects used from SqlTableDependency.

If i did not correctly understand, please contact me to resolve your issue.
Jul 11, 2016 at 1:58 AM
Edited Jul 11, 2016 at 2:26 AM