We recently identified a problem with one of our databases where as a result of a 'fire & forget' setup (i.e: conversations being closed immediately after sending), our sys.conversation_endpoints table was filling up with DI/DISCONNECTED_INBOUND messages. This eventually spilled over into the tempDB, causing it to grow enormously and eat up precious disk space. We eventually resolved this issue by commenting out the line
END CONVERSATION @handle WITH CLEANUP
in our sending SP and closing the conversations in our receiving SP using the same code,
END CONVERSATION @handle WITH CLEANUP
However, we now have a new issue. Since moving servers (and migrating from SQL Server 2005 to SQL Server 2008) we've recently discovered that sys.conversation_endpoints is now filling up with CO/CONVERSING messages, indicating that the conversations are not being closed. The receiving SP is closing them, or at least is running the command to do so, so I don't understand where these messages are coming from.
I've tried going back to ending the conversation at the point of send, but it has no effect. Is it wrong to end conversations on the receiving end using WITH CLEANUP? Or is there some other problem?
This post on techtarget seems to suggest its a bug, and that running a job to cleanup the leftovers is the only solution...
UPDATE: Pawel pointed out below that I should be avoiding the Fire & Forget Pattern, and I've added an activated SP to the initiator queue to end any conversations. However, sys.conversation_endpoints is STILL filling up, this time with CD/CLOSED messages. Here's the structure of my queues
Send_SP:
DECLARE @h UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @h
FROM SERVICE 'InitiatorQueue' TO SERVICE 'TargetQueue'
ON CONTRACT 'MyContract' WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @h MESSAGE TYPE 'MyMessage' (@msg)
Receive_SP (Activated SP on TargetQueue)
DECLARE @type SYSNAME, @h UNIQUEIDENTIFIER, @msg XML;
DECLARE @target TABLE (
[message_type_name] SYSNAME,
[message_body] VARBINARY(MAX),
[conversation_handle] UNIQUEIDENTIFIER
)
WHILE(1=1)
BEGIN TRANSACTION
WAITFOR(RECEIVE TOP (1000)
[message_type_name],[message_body],[conversation_handle]
FROM TargetQueue INTO @target), TIMEOUT 2000
IF(@@rowcount!=0)
BEGIN
WHILE((SELECT count(*) FROM @target) > 0)
BEGIN
SELECT TOP (1) @type = [message_type_name],
@msg = [message_body],
@h = [conversation_handle] FROM @target;
// Handle Message Here
END CONVERSATION @h;
DELETE TOP (1) FROM @target;
END
END
COMMIT TRANSACTION;
End_SP (Activated SP on InitiatorQueue)
DECLARE @type SYSNAME, @h UNIQUEIDENTIFIER, @msg XML;
DECLARE @init TABLE (
[message_type_name] SYSNAME,
[message_body] VARBINARY(MAX),
[conversation_handle] UNIQUEIDENTIFIER
)
WHILE(1=1)
BEGIN TRANSACTION
WAITFOR(RECEIVE TOP (1000)
[message_type_name],[message_body],[conversation_handle]
FROM InitiatorQueue INTO @init), TIMEOUT 2000
IF(@@rowcount!=0)
BEGIN
WHILE((SELECT count(*) FROM @init) > 0)
BEGIN
SELECT TOP (1) @type = [message_type_name],
@msg = [message_body],
@h = [conversation_handle] FROM @init;
END CONVERSATION @h;
DELETE TOP (1) FROM @init;
END
END
COMMIT TRANSACTION;