title: How to clean up after CREATE DIALOG CONVERSATION on disconnect?
body: <p>---Edited to include more and better details---</p>
  
  <p>In SQL Server, you can initialize a conversation on a queue:</p>
  
  <p><a href="https://msdn.microsoft.com/en-us/library/ms187377.aspx" rel="nofollow noreferrer">https://msdn.microsoft.com/en-us/library/ms187377.aspx</a></p>
  
  <p>Multiple processes will all initialize conversations, store the @dialog_handle in table [dbo].[ActiveConversations], and then WAITFOR a message to arrive (from a trigger on a table).</p>
  
  <p>Here's the gist of what the code is doing (leaving out some irrelevant
  complications and error checking and such):</p>
  
  <p>Starting the conversation via .NET SqlCommand:</p>
  
  <pre><code>DECLARE @LoginTime datetime;
  DECLARE @handle uniqueidentifier;
  SELECT TOP 1 @LoginTime = login_time FROM sys.sysprocesses WHERE spid = @@SPID;
  BEGIN DIALOG @handle
     FROM SERVICE [myService]
     TO SERVICE 'myService'
     ON CONTRACT myContract
     WITH ENCRYPTION=OFF;
  
  INSERT INTO [dbo].[ActiveConversations]
  (
     ConversationHandle,
     SysProcessID,
     SysProcessLoginTime
  )
  VALUES
  (
     @handle,
     @@SPID,
     @LoginTime
  )
  </code></pre>
  
  <p>Ending the conversation via .NET SqlCommand:</p>
  
  <pre><code>DECLARE @handle uniqueidentifier;
  SELECT TOP 1 @handle = ConversationHandle
     FROM [dbo].[ActiveConversations] AS conv
     INNER JOIN sys.sysprocesses as sysp
        ON conv.SysProcessLoginTime = sysp.login_time;
  
  DELETE FROM [dbo].[ActiveConversations] WHERE ConversationHandle = @handle;
  END CONVERSATION @handle;
  </code></pre>
  
  <p>Sending messages:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[myTableChanged] ON [dbo].[myTable] AFTER UPDATE
  AS
  BEGIN
     DECLARE @handle uniqueidentifier;
     DECLARE curs CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
     FOR
     SELECT ConversationHandle FROM [dbo].[ActiveConversations];
  
     OPEN curs
     FETCH NEXT FROM curs INTO @handle;
     WHILE @@FETCH_STATUS = 0
     BEGIN
        BEGIN TRY
           SEND ON CONVERSATION @handle
              MESSAGE TYPE [myType] ( '' );
        END TRY
        BEGIN CATCH
        END CATCH
        FETCH NEXT FROM curs INTO @ConversationHandle;
     END
     CLOSE curs;
     DEALLOCATE curs;
  END
  </code></pre>
  
  <p>Waiting for messages:</p>
  
  <pre><code>DECLARE @handle uniqueidentifier;
  SELECT @handle = [ConversationHandle]
      FROM [dbo].ActiveConversations] AS conv
      INNER JOIN sys.sysprocesses AS sysp
      ON sysp.spid = conv.SysProcessID
      AND sysp.login_time = conv.SysProcessLoginTime
      AND sysp.spid = @@SPID;
  
  WAITFOR (RECEIVE * FROM [dbo].[myQueue] WHERE conversation_handle = @handle);
  </code></pre>
  
  <p>When each process exits, it is responsible for calling END CONVERSATION.  However, if a process were to die an untimely death, it will never get a chance to call END CONVERSATION, and the conversation will sit out there forever.  Or will it?</p>
  
  <p>Will the conversation be cleaned up automatically, or how do you make sure dead conversations don't accumulate?</p>
  
  <p>What's the best practice in this scenario?  Is it to set a specific timeout and then re-initialize the conversation periodically?  If the conversation ends unexpectedly, is there a way to find out so it can be deleted from the [dbo].[ActiveConversations] table?</p>