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

by

Troubleshooting Guide: SQL Server Error 9218 – Query Notification Object Limit Reached

Why This Error Happens

Error 9218 occurs when the internal Query Notification subsystem (part of the Service Broker infrastructure) exceeds its maximum allowable number of active notification objects, typically because applications are subscribing to notifications without properly disposing of SqlDependency or SqlNotificationRequest objects. This commonly results from connection pooling issues, applications not calling SqlDependency.Stop(), or a runaway loop that continuously registers new notification subscriptions. Over time, these orphaned notification objects accumulate in memory until SQL Server’s internal cap (tied to available memory and configured limits) is reached, causing new subscription requests to fail.

Step-by-Step Resolution Guide

Step 1: Identify Applications Generating Excessive Notifications

  • Query the sys.dm_qn_subscriptions DMV to see active Query Notification subscriptions and their counts.
  • Run: SELECT COUNT(*) AS ActiveSubscriptions, database_id FROM sys.dm_qn_subscriptions GROUP BY database_id;
  • Cross-reference the database_id with application connection strings to isolate the offending service or app pool.

Step 2: Clean Up Orphaned Subscriptions and Restart the Dependency Service

  • On the application server, ensure every SqlDependency.Start() call has a matching SqlDependency.Stop() call during app shutdown or recycle events.
  • For ASP.NET apps on IIS (Windows Server 2025), recycle the affected Application Pool via iisreset /restart or through IIS Manager to force disposal of stale SqlDependency objects.
  • If subscriptions remain stuck, manually clear them by disabling and re-enabling notifications on the affected database:
  • ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  • ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Increase Memory Allocation or Adjust Notification Limits

  • Verify SQL Server has adequate memory allocated via sp_configure 'max server memory' since Query Notifications consume memory from the Service Broker memory clerk.
  • If running on Windows Server 2025 with Hyper-V, confirm the VM isn’t memory-constrained by checking Hyper-V dynamic memory settings against actual workload needs.
  • Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) after configuration changes to clear the internal object cache.

Step 4: Audit and Limit Notification Scope in Application Code

  • Review application queries used with SqlDependency to ensure they meet the strict requirements for notification eligibility (no SELECT *, two-part table names, deterministic functions only).
  • Reduce the polling/subscription frequency in application logic to avoid rapid re-registration of notification requests (a common cause of exponential object growth).

Alternative or Verification Steps

  • After applying fixes, re-run SELECT COUNT(*) FROM sys.dm_qn_subscriptions; and confirm the count stabilizes well below previous levels without continuously climbing.
  • If the error persists under legitimate high-volume use, consider migrating to SQL Server Service Broker with explicit queues instead of SqlDependency, as it offers more granular control and higher scalability for notification-heavy workloads.