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

by

Troubleshooting Guide: SQL Server Error 9669 – “Conversation Endpoints Analyzed: %d”

Why This Error Happens

This informational/diagnostic message is generated by the Service Broker engine during its internal cleanup task (sysdesend/sysdercv cleanup) when it scans and analyzes conversation endpoints for orphaned or expired dialogs. It typically surfaces alongside broader Service Broker health issues, such as a buildup of unclosed conversations, corrupted dialog metadata, or a backlog in the sys.conversation_endpoints system view that the engine cannot resolve automatically. In most cases, the root cause is an application that opens Service Broker dialogs but fails to properly call END CONVERSATION, causing the endpoint count to grow until SQL Server logs this diagnostic entry.

Step-by-Step Resolution Guide

Step 1: Identify the Scope of Orphaned Conversations

  • Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
  • Run the following query to check endpoint volume and state:
SELECT state_desc, COUNT(*) AS EndpointCount
FROM sys.conversation_endpoints
GROUP BY state_desc;
  • Look specifically for endpoints in the CLOSED or DISCONNECTED_INBOUND/DISCONNECTED_OUTBOUND states, as these are the primary contributors to the analyzed count in Error 9669.

Step 2: Clean Up Orphaned or Expired Dialogs

  • For databases with Service Broker enabled, identify and end stale conversations manually using:
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE convo_cursor CURSOR FOR
SELECT conversation_handle FROM sys.conversation_endpoints
WHERE state = 'CD'; -- Closed state
OPEN convo_cursor;
FETCH NEXT FROM convo_cursor INTO @handle;
WHILE @@FETCH_STATUS = 0
BEGIN
    END CONVERSATION @handle WITH CLEANUP;
    FETCH NEXT FROM convo_cursor INTO @handle;
END
CLOSE convo_cursor;
DEALLOCATE convo_cursor;
  • Ensure this is run during a maintenance window, as WITH CLEANUP forcibly removes conversation state without notifying the remote endpoint.

Step 3: Verify Service Broker Configuration Integrity

  • Confirm Service Broker is enabled and not corrupted at the database level:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If disabled unexpectedly or the GUID is mismatched, repair it with:
ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
  • On Windows Server 2025/Windows 11, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating Broker errors (e.g., Error 9675 or 9676) that often accompany 9669.

Step 4: Adjust the Diagnostic Interval (Optional Suppression)

  • If the message is purely informational and recurring due to normal high-volume messaging workloads, you can reduce log noise by adjusting the trace flag behavior.
  • Use Trace Flag 1222 or Extended Events to monitor Broker activity instead of relying on the default ERRORLOG entry, preventing alert fatigue from this specific event.

Alternative or Verification Steps

  • Verify the fix: Re-run the sys.conversation_endpoints query from Step 1; the total count should stabilize or decrease after cleanup, and Error 9669 entries in the SQL Server log should no longer show an increasing %d value.
  • Alternative solution: If orphaned endpoints continue to accumulate rapidly, audit the application code for missing END CONVERSATION calls or implement a scheduled SQL Agent job to run the cleanup script in Step 2 nightly as a permanent maintenance solution.