如何清理sys.conversation_endpoints

Question

我有一个表，表上的触发器是使用service broker实现的。每天有超过50万条记录插入到表中。

异步SP用于通过插入的数据检查几个状态，并更新其他表。它在过去的1个月中运行良好，并且SP在插入记录的2-3秒内得到执行。但现在需要90多分钟。

目前sys.conversation_endpoints的记录太多了。

(请注意，所有的表每天都会被截断，因为我第二天不需要这些记录)

其他数据库活动正常(平均CPU利用率为60% )。

现在我需要看哪里？？

我可以重新创建数据库而没有任何问题，但我认为这不是解决问题的好方法

Answer 1

你可以‘结束对话@handle’或‘结束对话@handle with cleanup’

Answer 2

用于结束特定服务的所有会话的脚本：

DECLARE @sql NVARCHAR(MAX)
       ,@far_service NVARCHAR(256) = N'far_service_name';

WHILE EXISTS (
                 SELECT COUNT(*)
                 FROM   sys.conversation_endpoints
                 WHERE  far_service = @far_service
             )
BEGIN
    SET @sql = N'';
    SELECT TOP 1000 @sql = @sql + N'END CONVERSATION ''' + CAST(conversation_handle AS NVARCHAR(50)) + N''' WITH CLEANUP;
'
    FROM   sys.conversation_endpoints
    WHERE  far_service = @far_service;

    --PRINT @sql;
    EXEC sys.sp_executesql @stmt = @sql;

    --RETURN;
END;
GO

Answer 3

在修改@thesqldev中的代码后，以下是我的解决方案

USE [MY_DATABASE_NAME_HERE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[mqConversationsClearAll]

AS

-- Note: you can check the queue by running this query
-- SELECT * FROM sys.conversation_endpoints GO


DECLARE @getid CURSOR
        ,@sql NVARCHAR(MAX)
        ,@conv_id NVARCHAR(100)
        ,@conv_handle NVARCHAR(100)
        ,@conv_service NVARCHAR(100)
        -- ,@far_service NVARCHAR(256) = N'One_Specific_Service_Target';

-- want to create and execute a chain of statements like this, one per conversation
-- END CONVERSATION 'FE851F37-218C-EA11-B698-4CCC6AD00AE9' WITH CLEANUP;
-- END CONVERSATION 'A4B4F603-208C-EA11-B698-4CCC6AD00AE9' WITH CLEANUP;    

SET @getid = CURSOR FOR
                SELECT [conversation_id], [conversation_handle], [far_service]
                FROM   sys.conversation_endpoints
                WHERE NOT([State] = 'CO') 
                -- CO = CONVERSING [State]
                -- alternatively, might like one service name only
                -- WHERE  far_service = @far_service

OPEN @getid

    FETCH NEXT
    FROM @getid INTO @conv_id, @conv_handle, @conv_service
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql = 'END CONVERSATION ' + char(39) + @conv_handle + char(39) + ' WITH CLEANUP;'
            EXEC sys.sp_executesql @stmt = @sql;
            FETCH NEXT
            FROM @getid INTO @conv_id, @conv_handle, @conv_service
        END

CLOSE @getid
DEALLOCATE @getid

然后，只要您想要清除sys.conversation_endpoints，就可以执行新创建的存储过程“mqConversationsClearAll