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

by

Troubleshooting Guide: Error 9217 – Failed to Drop Unused Internal Query Notification Table

Why This Error Happens

Error 9217 occurs when SQL Server’s Service Broker attempts to clean up internal query notification tables but cannot complete the drop operation, typically due to the table being held open by an active transaction or an orphaned session lock. This can also result from Service Broker corruption, insufficient permissions on system objects, or disk I/O issues preventing metadata updates during the cleanup routine. In most cases, this is a transient issue tied to Query Notification (used by SqlDependency or SqlCacheDependency) rather than a sign of critical database corruption.

Step-by-Step Resolution Guide

Step 1: Identify Active Sessions Blocking the Internal Table

  • Run the following query to check for blocking sessions related to Service Broker or system transactions:
SELECT session_id, blocking_session_id, wait_type, wait_resource 
FROM sys.dm_exec_requests 
WHERE wait_type LIKE '%BROKER%' OR wait_type LIKE '%LCK%';
  • Identify any long-running or orphaned sessions using sys.dm_exec_sessions and terminate them if safe using KILL <session_id>.

Step 2: Verify and Repair Service Broker Configuration

  • Check if Service Broker is enabled on the affected database:
SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If disabled unexpectedly or in an inconsistent state, re-enable it safely:
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
  • Ensure the Service Broker GUID is valid and not duplicated across restored databases using sys.databases.service_broker_guid.

Step 3: Run Database Consistency Checks

  • Execute a full integrity check to rule out underlying corruption in system catalogs:
DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review the output for any allocation or consistency errors tied to internal system tables (object IDs starting with negative values, typical of Query Notification tables).

Step 4: Clear Orphaned Query Notification Subscriptions

  • Manually clean up stale SqlDependency/Query Notification subscriptions if your application uses SqlDependency.Start():
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
  • Restart the SQL Server service to force a clean reinitialization of internal Broker queues:
  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Locate SQL Server (MSSQLSERVER) or your named instance.
  • Right-click and select Restart.

Step 5: Apply Latest Cumulative Update

  • Confirm your SQL Server 2025 build number via:
SELECT @@VERSION;
  • Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as this specific internal cleanup bug is often patched in maintenance releases.

Alternative or Verification Steps

  • After applying fixes, monitor the SQL Server Error Log (Management > SQL Server Logs in SSMS) for 24–48 hours to confirm Error 9217 does not recur during off-peak cleanup cycles.
  • If the error persists, as a workaround, disable and re-enable Service Broker entirely on the database (ALTER DATABASE ... SET DISABLE_BROKER; followed by SET ENABLE_BROKER;) to force SQL Server to rebuild its internal notification table structures from scratch.