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

by

Troubleshooting Guide: SQL Server Error 9206 – Query Notification Subscription Cannot Be Deleted

Why This Error Happens

This error occurs when an application or internal process attempts to explicitly remove a Query Notification subscription using sys.sp_dispatcher_end_dialog or similar cleanup logic, but the subscription ID (%ld) has already been removed from the internal notification queue. This typically happens because the notification already “fired” naturally (the underlying data changed or the subscription timed out) before the manual deletion request was processed, creating a race condition. In some cases, this is also triggered by corrupted Service Broker queues or orphaned conversation handles left behind after an application crash or improper connection termination.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status

  • Confirm that Service Broker is enabled for the specific database, as Query Notifications rely entirely on this feature.
  • Run the following T-SQL query to check the status:
  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If is_broker_enabled returns 0, enable it immediately using ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;.

Step 2: Inspect and Clear Orphaned Conversations

  • Check the sys.conversation_endpoints system view to identify dialogs stuck in a CLOSED or DISCONNECTED_INBOUND state that haven’t been garbage collected.
  • Execute the following to view problematic endpoints:
  SELECT conversation_handle, state_desc, far_service FROM sys.conversation_endpoints;
  • If a high volume of stale endpoints exists, manually end them using END CONVERSATION conversation_handle WITH CLEANUP; to force the removal of the orphaned subscription data.

Step 3: Update Application Logic for Race Conditions

  • Modify the application code (likely utilizing SqlDependency or SqlNotificationRequest in .NET) to wrap the unsubscribe logic in a try-catch block specifically handling SQL Exception 9206.
  • Since this error is often benign (indicating the resource is already gone), ensure the application logic treats this specific exception as a “soft success” rather than a critical failure requiring a retry loop.

Step 4: Restart the SQL Server Service Broker Task

  • If errors persist across multiple databases, restart the SQL Server service to reset the internal Service Broker task manager.
  • On Windows Server 2025, open Services.msc, locate SQL Server (MSSQLSERVER), right-click, and select Restart.
  • Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.

Alternative or Verification Steps

  • Verification: After implementing the fix, monitor the SQL Server Error Log via EXEC sp_readerrorlog; for 24 hours to ensure Error 9206 does not reappear during standard application polling cycles.
  • Alternative: If the issue persists due to high-frequency triggers, consider migrating the dependency logic to SignalR or a dedicated Change Tracking mechanism, as Query Notifications are generally recommended only for low-frequency update scenarios in SQL Server 2025.