Service Broker leaving closed items in sys.conversation_endpoints after 30 mins

Question

I have a single machine service broker set up using internal activation, the broker is processing items that are placed on the queue and appears to be closing the conversations correctly (see profile details below).

A single record left in sys.conversation_endpoints in a CD - CLOSED state with a security_timestamp that is set 30 mins in the future but this record is never deleted.

I also have my own log shows the what its happening the only thing that appears strange is that the initiator procedure gets called with null values for conversation_handle, message_type and message_body (last item in my log information)

I have included a script (name changed not indicative of actual names) that will create all the objects I'm using, and to mimic how I am using them simply execute:

dbo.usp_QueueRequest 2, null, 'Trial'

That should then give you the same results that I have been seeing consistently

Trace:

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

My log

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.converstation_endpoints record (shortened)

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

Script to create objects (names changed, not actual names)

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

From my understanding and experience, once the conversation is CLOSED you don't have do anything. SQL Server will automatically manage the cleanup of the conversation for you.

If you really want those rows to go away; when you call END CONVERSATION then you should append WITH CLEANUP which will cause SQL Server to clean up the conversation as part of that transaction. It is my understanding, however, that you get better performance by letting SQL Server manage the cleanup for you.