sql-server-2008 - Service Broker 在 30 分钟后将关闭的项目留在 sys.conversation_endpoints

Question

我有一个使用内部激活设置的单机服务代理，该代理正在处理放置在队列中的项目，并且似乎正在正确关闭对话(请参阅下面的配置文件详细信息)。

sys.conversation_endpoints 中的单个记录以 CD - CLOSED 状态保留，security_timestamp 设置为 future 30 分钟，但该记录永远不会被删除。

我也有我自己的日志显示它发生了什么唯一看起来很奇怪的是启动程序被调用时调用了 session 句柄、消息类型和消息主体的空值(我的日志信息中的最后一项)

我已经包含了一个脚本(名称更改不表示实际名称)，它将创建我正在使用的所有对象，并模仿我如何使用它们只需执行:

dbo.usp_QueueRequest 2, null, 'Trial'

那应该会给你我一直看到的相同结果

痕迹:

EventClass                TextData              SPID Event Sequence EventSubClass
Broker:Activation                               30   1394           1 - Start
Broker:Conversation       CONVERSING            30   1395           1 - SEND Message
Broker:Message Classify                         30   1396           1 - Local
Broker:Conversation       CONVERSING            30   1397           6 - Received Sequenced Message
Broker:Activation                               31   1398           1 - Start
Broker:Conversation       DISCONNECTED_OUTBOUND 31   1399           2 - END CONVERSATION
Broker:Conversation Group                       31   1400           2 - Drop
Broker:Message Classify                         31   1401           1 - Local
Broker:Conversation       DISCONNECTED_INBOUND  31   1402           7 - Received END CONVERSATION
Broker:Conversation       CLOSED                31   1403           10 - Received END CONVERSATION Ack
Broker:Conversation       CLOSED                30   1404           2 - END CONVERSATION
Broker:Conversation Group                       30   1405           2 - Drop
Broker:Activation                               30   1406           2 - Ended
Broker:Activation                               31   1407           2 - Ended

我的日志

Sequence Description
1        Target activation for message type: RequestMessage
2        Call fake processing procedure
3        In fake processing procedure
4        Send ReplyMessage for conversation 8B355311-5D9F-E111-93EC-A4BADBFBB13B
5        Initiator activation
6        Initiator conversation 88355311-5D9F-E111-93EC-A4BADBFBB13B message type: ReplyMessage
7        Initiator received reply from target, end converstion
8        Initiator activation
9        Target activation for message type: http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
10       Target received end dialog
11       Initiator No conversation message type:No message type

sys.convertation_endpoints 记录(缩短)

conversation_handle                     is_initiator state  state_desc far_service      security_timestamp      dialog_timer
8B355311-5D9F-E111-93EC-A4BADBFBB13B    0            CD     CLOSED     InitiatorService 2012-05-16 14:13:38.923 1900-01-01 00:00:00.000

创建对象的脚本(名称已更改，而不是实际名称)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Log]') AND type in (N'U'))
DROP TABLE [dbo].[Log]
GO

CREATE TABLE [dbo].[Log](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [varchar](max) NULL,
    [DateTime] [date] NULL  DEFAULT (getdate()))

GO

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BrokerUser')
DROP USER [BrokerUser]
GO

CREATE USER [BrokerUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO

IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'RequestMessage')  
    CREATE MESSAGE TYPE [RequestMessage] VALIDATION = WELL_FORMED_XML

IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'AReplyMessage')   
    CREATE MESSAGE TYPE [ReplyMessage] VALIDATION = WELL_FORMED_XML
GO

IF NOT EXISTS (SELECT * FROM sys.service_contracts WHERE name =N'MessageContract')
    CREATE CONTRACT [MessageContract]
        ([RequestMessage] SENT BY INITIATOR,
        [ReplyMessage] SENT BY TARGET)
GO  

IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue')
    CREATE QUEUE TargetQueue
GO

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue')
    GRANT RECEIVE ON [TargetQueue] TO [BrokerUser]
GO

IF NOT EXISTS (SELECT * FROM sys.services WHERE name = N'TargetService')
    CREATE SERVICE [TargetService]
                   ON QUEUE TargetQueue
                   ([MessageContract])
GO

IF EXISTS (SELECT * FROM sys.services WHERE name = N'TargetService')
    GRANT SEND ON SERVICE::[TargetService] TO [BrokerUser]
GO

IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue')
    CREATE QUEUE InitiatorQueue;
GO  

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue')
    GRANT RECEIVE ON [InitiatorQueue] TO [BrokerUser]
GO

IF NOT EXISTS (SELECT * FROM sys.services WHERE name = N'InitiatorService')
CREATE SERVICE [InitiatorService]
               ON QUEUE InitiatorQueue
GO

IF EXISTS (SELECT * FROM sys.services WHERE name = N'InitiatorService') 
    GRANT SEND ON SERVICE::[InitiatorService] TO [BrokerUser]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FakeProcessing]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FakeProcessing]
GO

CREATE PROCEDURE [dbo].[usp_FakeProcessing]
AS
BEGIN

    insert [Log] ([Description])
    VALUES ('In fake processing procedure');

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TargetActivation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_TargetActivation]
GO

CREATE PROCEDURE [dbo].[usp_TargetActivation]
AS
  DECLARE @Handle UNIQUEIDENTIFIER;
  DECLARE @Message XML;
  DECLARE @MessageType sysname;

  WHILE (1=1)
  BEGIN

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
        @Handle = conversation_handle,
        @Message = message_body,
        @MessageType = message_type_name
      FROM TargetQueue
    ), TIMEOUT 5000;

    IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END


    INSERT [Log] ([Description]) VALUES ('Target activation for message type: ' + + COALESCE(@MessageType,'No message type'))

    -- it's an error message (some error occurred on the initiator side).  
    --This type of message is automatically created by SB when an END CONVERSATION command is executed with the WITH ERROR clause for the initiator
    IF @MessageType =N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' 
    BEGIN

        ROLLBACK TRANSACTION; -- roll back the message transaction.  In this example there was nothing done to roll back but it is good practice
                              -- in the event of an error

        INSERT [Log] ([Description]) VALUES ('Error occured with SB')
       END CONVERSATION @Handle; -- and end the conversation (dialog) on this side (target)
       BREAK; -- break out of the loop (and end the stored proc)

    END
    ELSE IF @MessageType = N'RequestMessage'
    BEGIN


        INSERT [Log] ([Description]) VALUES ('Call fake procedure')

        EXEC [usp_FakeProcessing]; 

        INSERT [Log] ([Description]) VALUES ('Add reply message for conversation ' + CAST(@Handle AS varchar(80)) + ' with message : ' + CAST(@Message as VARCHAR(4000)));

        SEND ON CONVERSATION @Handle
                MESSAGE TYPE [ReplyMessage] (@message)

    END
    ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
        INSERT [Log] ([Description]) VALUES ('Target received end dialog')
       END CONVERSATION @Handle;
    END    

    COMMIT TRANSACTION;

  END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InitiatorActivation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_InitiatorActivation]
GO

CREATE PROCEDURE [dbo].[usp_InitiatorActivation]
AS

  DECLARE @Handle UNIQUEIDENTIFIER;
  DECLARE @Message XML;
  DECLARE @MessageType sysname;

BEGIN

    INSERT [Log] ([Description]) VALUES ('Initiator activation')

    WAITFOR
    ( RECEIVE TOP(1)
        @Handle = conversation_handle,
        @Message = message_body,
        @MessageType = message_type_name
      FROM InitiatorQueue
    ), TIMEOUT 5000;


    INSERT [Log] ([Description]) VALUES ('Initiator ' + ISNULL(CAST(@Handle AS VARCHAR(100)) , 'No handle') + ' message type:' + COALESCE(@MessageType,'No message type') + ' Body: ' + COALESCE(CAST(@Message AS varchar(1000)),'No body'))

    IF  @Handle IS NOT NULL
    BEGIN

        IF @MessageType = N'ReplyMessage'
        BEGIN
            INSERT [Log] ([Description]) VALUES ('Initiator received reply from target, end converstion')
           END CONVERSATION @Handle;
        END  


        IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
        BEGIN
            INSERT [Log] ([Description]) VALUES ('Initiator received end dialog message')
           END CONVERSATION @Handle;
        END  
    END
END
GO

GRANT EXECUTE ON [usp_TargetActivation] to [BrokerUser]
GO

GRANT EXECUTE ON [usp_InitiatorActivation] to [BrokerUser]
GO

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue')
ALTER QUEUE TargetQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = [usp_TargetActivation],
      MAX_QUEUE_READERS = 1,
      EXECUTE AS 'BrokerUser'
    );
GO

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue')
ALTER QUEUE InitiatorQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = [usp_InitiatorActivation],
      MAX_QUEUE_READERS = 1,
      EXECUTE AS 'BrokerUser'
    );
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_QueueRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_QueueRequest]
GO

CREATE PROCEDURE [dbo].[usp_QueueRequest]
    @numericvalue INT,
    @StartDate DATE,
    @TriggeredBy VARCHAR(50)
AS
BEGIN
    DECLARE @Handle UNIQUEIDENTIFIER;
    DECLARE @RequestMsg NVARCHAR(100);

    DECLARE @MessageBody XML
    DEClare @Params TABLE
    (
        ReadingDate SMALLDATETIME,
        SiteNumber INT,
        TriggeredBy VARCHAR(50)
    )
    INSERT INTO @Params(ReadingDate,SiteNumber, TriggeredBy)
    VALUES(@StartDate, @numericvalue, @TriggeredBy)

    SELECT @MessageBody = (SELECT * FROM @Params FOR XML PATH ('Params'), TYPE);

    BEGIN TRANSACTION;

    BEGIN DIALOG @Handle
            FROM SERVICE [InitiatorService]
            TO SERVICE  'TargetService'
            ON CONTRACT [MessageContract]
            WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @Handle
         MESSAGE TYPE 
            [RequestMessage]
            (@MessageBody);

    COMMIT TRANSACTION;
END
GO

Answer 1

根据我的理解和经验，一旦对话结束，你就什么也做不了。 SQL Server 将自动为您管理对话的清理。

如果您真的希望这些行消失；当您调用 END CONVERSATION那么你应该附加 WITH CLEANUP这将导致 SQL Server 将对话作为该事务的一部分进行清理。然而，我的理解是，让 SQL Server 为您管理清理工作可以获得更好的性能。