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

by

Troubleshooting Guide: SQL Server Error 10085 – “RestartPosition on the table was canceled during notification”

Why This Error Happens

This error is generated by SQL Server’s Query Notification / Service Broker-based change tracking mechanism (often tied to SqlDependency or SqlNotificationRequest) when the underlying subscription is invalidated before the notification can be delivered. The most common root cause is that the table schema was altered, the connection was dropped, or Service Broker was disabled/stopped while a notification subscription was still active and monitoring that table. It can also occur due to corrupted Service Broker queues, conversation timeouts, or insufficient permissions on the SUBSCRIBE QUERY NOTIFICATIONS database permission for the executing login.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

  • Run the following query to check Service Broker status: SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
  • If is_broker_enabled returns 0, enable it with: ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
  • On Windows Server 2025/Windows 11 hosts, ensure no pending database backup or restore operations are locking the database when you run this command, as ENABLE_BROKER can hang if there are open transactions.

Step 2: Check and Repair SUBSCRIBE QUERY NOTIFICATIONS Permissions

  • Confirm the application’s SQL login has the correct permission: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourAppLogin];
  • Inspect sys.transmission_queue and sys.conversation_endpoints for orphaned or errored conversations tied to the notification queue.
  • If you find stuck conversations, end them manually: END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

Step 3: Clear and Reset Stale Query Notification Subscriptions

  • Stop the application or service generating the SqlDependency/SqlNotificationRequest calls temporarily.
  • Drop and recreate the Service Broker queue and service associated with notifications: DROP SERVICE [YourNotificationService]; DROP QUEUE [YourNotificationQueue]; then recreate them using your original deployment script.
  • Restart the SQL Server Service Broker task via: ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; ALTER DATABASE YourDatabaseName SET ENABLE_BROKER; — this forces a clean restart of the broker task handling restart positions.

Step 4: Validate Underlying Table Schema Stability

  • Ensure no DDL changes (ALTER TABLE, index rebuilds with schema modification, etc.) are occurring on the monitored table while notifications are active, as any schema change immediately invalidates and cancels the subscription (triggering Error 10085 by design).
  • If schema changes are required, re-issue the SqlDependency/notification subscription immediately after the DDL operation completes.

Step 5: Check SQL Server and OS-Level Event Logs

  • Open Event Viewer on Windows Server 2025 / Windows 11 and navigate to Applications and Services Logs > MSSQLSERVER to correlate Error 10085 with any Service Broker (Error 9617) or network-related errors (Error 64, 10053) occurring at the same timestamp.
  • Cross-reference with the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to identify if broker transport failures are the underlying trigger.

Alternative or Verification Steps

  • To verify the fix, re-run your application’s notification subscription and monitor sys.dm_qn_subscriptions to confirm the subscription remains in an active state without being dropped after a few minutes.
  • If the error persists, as an alternative, switch from classic Query Notifications to SQL Server Change Tracking or Change Data Capture (CDC), which are more resilient on SQL Server 2025 and avoid the Service Broker notification cancellation issue entirely.