Clearing data from sys.conversation_endpoints

Jan 16, 2016 at 8:03 AM
Edited Jan 16, 2016 at 8:06 AM
Hello ,
When recieving notifications from service broker , it also generates an endpoint in table 'sys.conversation_endpoints' and I have 30 columns in table and foreach column per request , it adds new endpoint , what is the exact deletion time of these points , I have checked stop() method is not removing these records and in five days my database file increases from 1 to 5 GB. Can you help me regarding that , for now i have removed the records and ended the conversation using this SP and disabled the service broker for now


CREATE PROCEDURE CleanBrokerConversations
AS
BEGIN
BEGIN TRY
    -- create a memory table. I dont prefer cursors usually
    DECLARE @t TABLE( AutoID INT IDENTITY, ConversationHandle UNIQUEIDENTIFIER)

    -- insert the handles of all open conversations to the 
    -- memory table
    INSERT INTO 
        @t (ConversationHandle)
    SELECT 
        [conversation_handle] 
    FROM
        sys.conversation_endpoints

    -- local variables
    DECLARE @cnt INT, @max INT, @handle UNIQUEIDENTIFIER
    SELECT @cnt = 1, @max = COUNT(*) FROM @t

    -- run a loop for each row in the memory table
    WHILE @cnt <= @max BEGIN
        -- read the conversation_handle
        SELECT
            @handle = ConversationHandle
        FROM @t WHERE AutoID = @cnt

        -- end conversation
        PRINT 'Closing conversation: ' + CAST(@handle AS VARCHAR(50))
        END CONVERSATION @handle WITH CLEANUP

        -- increment counter
        SELECT @cnt = @cnt + 1
    END

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
END
Coordinator
Jan 19, 2016 at 6:44 AM
Hello Ammar,

sorry for the delay of my response.

Yesterday evening I run the unit tests and then I checked [conversation_handle] table. I noticed a lot of endpoint, as you told me.
However, those endpoints are automatically remove after some time, in fact this morning my [conversation_handle] table was empty.
In any case, I agree with you about the necessity to remove a specific endpoint once Stop() is called.

I will do this fix as soon as possible.
Be patient because I am full of work.

Regards
Coordinator
Jan 28, 2016 at 10:21 AM
Hi.

The conversation endpoint will be reclaimed in 30 minutes after the lifetime expires.
So, the records in 'sys.conversation_endpoints' should disappear automatically after 30 min.

Do you still have this problem ?
Jan 28, 2016 at 3:20 PM
I see the lifetime it shows is

lifetime state state_desc
2084-02-15 18:43:29.463 SO STARTED_OUTBOUND
2084-02-13 23:21:49.720 SO STARTED_OUTBOUND

Regards,
Zak
Coordinator
Jan 29, 2016 at 11:24 AM
Taken from
Pro SQL Server 2008 Service Broker
by Klaus Aschenbrenner
apress

Replaying Service Broker Messages

You might think that you can replay a Service Broker message with changed data when you
capture one in the network (and also when the message is not signed). However, Service
Broker provides a mechanism that prevents replaying captured messages.
Unlike initiator endpoints, target endpoints of a conversation are not deleted immediately.
When you open a new conversation and close it, you can see on the target side in the
sys.conversation_endpoints catalog view that the conversation is in the CLOSED state
(state_desc column). But the conversation endpoint is deleted at least 30 minutes after
receiving the first acknowledgment message for a message sent by the target
. The threat
Service Broker tries to mitigate is the possibility that an attacker will capture a message on the
wire and replay it later, causing the opened dialog to perform the requested action again. Just
imagine that you send a message to request the withdrawal of money from your account . . .
When the initiator sends such a message, the target accepts it, performs the action, and
finally ends the dialog. Suppose that an attacker has intercepted this message and is capable
of resending it again. If the target endpoint was deleted, there would be no evidence in the
database that this dialog has already occurred once, so the target would be created again and
the action would be performed again. The attacker might repeat this again and again while the
message is valid. The messages have a timestamp on them, and the target accepts the messages
with a timestamp tolerance of 30 minutes, so the attacker would be able to repeat the
messages several times during those 30 minutes.
To prevent this, the target endpoint is not deleted for 30 minutes. If an attacker replays
the message during those 30 minutes, the endpoint already exists (in the CLOSED state), so the
message will be discarded. After 30 minutes, the target endpoint will be deleted, but by now
the message will have become out of date and won’t be accepted if replayed because the message
timestamp has expired.