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

by

Troubleshooting Guide: SQL Server Error 9788

Why This Error Happens

Error 9788 occurs when the Service Broker message routing engine attempts to query the msdb system database for routing information but cannot access it. This typically happens because msdb is offline, set to SINGLE_USER mode by another process, suspect due to corruption, or the SQL Server Agent/Service Broker service lacks the necessary permissions to read the routing tables. In some cases, this is also triggered during startup if msdb recovery has not yet completed when the first routing request is received.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of msdb

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following query to check database status:
  SELECT name, state_desc, user_access_desc 
  FROM sys.databases 
  WHERE name = 'msdb';
  • If state_desc shows anything other than ONLINE, proceed to Step 2.

Step 2: Bring msdb Back Online

  • If msdb is in RESTORING, RECOVERY_PENDING, or SUSPECT mode, attempt a manual recovery:
  ALTER DATABASE msdb SET EMERGENCY;
  DBCC CHECKDB('msdb');
  ALTER DATABASE msdb SET MULTI_USER;
  • If EMERGENCY mode is required, back up the database immediately afterward using the Windows Server 2025 Backup utility or sqlcmd.
  • Check the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for underlying I/O or corruption errors.

Step 3: Restart the SQL Server Service Broker and Agent

  • Open Services.msc on Windows Server 2025/Windows 11.
  • Restart SQL Server Agent (MSSQLSERVER).
  • Restart the main SQL Server (MSSQLSERVER) service to force Service Broker to re-initialize its routing cache.
  • Confirm Service Broker is enabled on msdb:
  SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

Step 4: Check File System and Permissions

  • Navigate to the data directory (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Ensure the msdbdata.mdf and msdblog.ldf files are not marked Read-Only in file properties.
  • Verify the NT Service\MSSQLSERVER account has Full Control permissions on the SQL Server data folder via the Security tab.

Step 5: Repair or Rebuild msdb (If Corruption Persists)

  • If DBCC CHECKDB reports unrecoverable corruption, restore msdb from the last known good backup.
  • As a last resort, rebuild system databases using the SQL Server 2025 setup media:
  setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators

Alternative or Verification Steps

  • To verify the fix, send a test message through the affected Service Broker route and confirm it processes without triggering Error 9788 in the Event Viewer (Windows Logs > Application).
  • If the issue persists after restarting services, check for antivirus or Windows Defender “Controlled Folder Access” policies on Windows Server 2025 that may be locking the msdb .mdf/.ldf files intermittently.