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

by

Troubleshooting Guide: SQL Server Error 9789 – Service Broker Routing Failure in msdb

Why This Error Happens

This error occurs when SQL Server’s Service Broker attempts to route an incoming message but cannot access the routing information stored in the msdb system database. The most common root cause is that the Service Broker feature has been explicitly disabled on msdb (either manually or due to a GUID mismatch after a database restore/migration). Additionally, this can happen if msdb is offline, suspect, or still in recovery mode, preventing SQL Server from reading the broker routing tables.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of msdb and Service Broker

  • Open SQL Server Management Studio (SSMS) or a query window connected via sqlcmd.
  • Run the following query to check the database status and broker configuration:
  SELECT name, state_desc, is_broker_enabled 
  FROM sys.databases 
  WHERE name = 'msdb';
  • If state_desc is not ONLINE, you must first bring msdb back online before proceeding to enable the broker.

Step 2: Enable the Service Broker on msdb

  • If the previous query showed is_broker_enabled as 0 (False), you need to enable it.
  • Since msdb cannot be set to SINGLE_USER mode easily without restarting dependencies, use the ALTER DATABASE command with the ERROR_BROKER_CONVERSATIONS termination clause to force the change:
  ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
  • Note: In a clustered or Always On environment, ensure you run this against the primary replica.

Step 3: Check for GUID Mismatches (Post-Restore Scenarios)

  • If Step 2 fails or reverts after a restart, verify if the service_broker_guid in msdb matches the dbid guid in the boot page (common after restoring msdb from a backup taken on a different server).
  • Run this comparison query:
  SELECT db_name(database_id), service_broker_guid, is_broker_enabled 
  FROM sys.databases;
  • If a mismatch is confirmed, you may need to use ALTER DATABASE msdb SET NEW_BROKER (Caution: This breaks existing conversation handles) or SET ENABLE_BROKER again after resolving the GUID conflict.

Step 4: Restart the SQL Server Service

  • On Windows Server 2025 / Windows 11, open Services.msc.
  • Locate SQL Server (MSSQLSERVER) or your named instance.
  • Right-click and select Restart to ensure the Broker’s internal activation stored procedures and routing tables are reloaded correctly into memory.

Alternative or Verification Steps

  • Verification: Re-run the query from Step 1. The is_broker_enabled column should now return 1. Monitor the SQL Server Error Log for approximately 5 minutes to ensure Error 9789 does not reappear.
  • Alternative: If the broker remains disabled due to a severely corrupted msdb, consider rebuilding the system databases using setup.exe /ACTION=REBUILDDATABASE followed by restoring a clean msdb backup, provided a very recent backup exists.