How to fix Error 9686 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 9686 — Conversation Endpoint Dropped

Why This Error Happens

This error occurs when SQL Service Broker automatically terminates a conversation endpoint because it exceeded the configured LIFETIME value or encountered an unrecoverable error during message exchange. It can also be triggered by corrupted dialog security credentials, network interruptions between broker endpoints, or when the receiving queue is disabled and messages back up past the retry threshold. In clustered or Always On environments, a failover mid-conversation can also cause Service Broker to drop the handle to maintain transactional consistency.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Queue Health

  • Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDB'; to confirm Service Broker is active.
  • Check SELECT * FROM sys.transmission_queue; for stuck or poison messages.
  • Ensure the target queue is not disabled: SELECT name, is_receive_enabled FROM sys.service_queues;.

Step 2: Inspect and Clean Up Orphaned Conversations

  • Query SELECT conversation_handle, state, lifetime FROM sys.conversation_endpoints; to locate expired or error-state conversations.
  • If a handle is in ER (error) or DI (disconnected) state, end it manually with END CONVERSATION 'conversation_handle' WITH CLEANUP;.
  • Restart affected services if cleanup does not clear the state: open Services.msc on Windows Server 2025 and restart SQL Server (MSSQLSERVER).

Step 3: Review Network and Endpoint Configuration

  • Confirm the Service Broker endpoint is listening: SELECT name, state_desc, port FROM sys.tcp_endpoints;.
  • Check Windows Firewall rules on both source and destination servers (default Service Broker port is 4022) via Windows Defender Firewall with Advanced Security.
  • Validate certificates used for dialog security haven’t expired: SELECT name, expiry_date FROM sys.certificates;.

Step 4: Increase Conversation Lifetime and Enable Error Logging

  • If conversations are timing out prematurely, adjust the LIFETIME parameter in your BEGIN DIALOG CONVERSATION statement to a longer duration (e.g., '00:30:00').
  • Enable Service Broker trace flags (2551) temporarily for deeper diagnostic logging via DBCC TRACEON (2551, -1);.
  • Review the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating entries around the Error 9686 timestamp.

Alternative or Verification Steps

  • To verify the fix, re-run the application workflow that triggers Service Broker messaging and monitor sys.conversation_endpoints to confirm new handles remain in CO (conversing) state without dropping.
  • If the issue persists, consider rebuilding the Service Broker GUID with ALTER DATABASE YourDB SET NEW_BROKER; (note: this invalidates all existing conversations, use only in non-production or during a maintenance window).