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

by

Troubleshooting Guide: SQL Server Error 9776 – Service Broker GUID Mismatch

Why This Error Happens

This error occurs when the Service Broker GUID stored inside the database’s internal metadata (the service_broker_guid recorded at the page level) does not match the value registered for that database in the sys.databases system catalog. This mismatch typically happens after a database restore, attach operation, or database copy where the Service Broker identifier was not properly reconciled, or when a database was restored with WITH KEEP_EXISTING / improper NEW_BROKER options. It can also occur after replicating a database file manually (copy-paste of MDF/LDF) rather than using supported SQL Server migration methods.

Step-by-Step Resolution Guide

Step 1: Identify the Current Broker GUID Status

  • Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance.
  • Run the following query to inspect the current state:
SELECT name, service_broker_guid, is_broker_enabled 
FROM sys.databases 
WHERE name = 'YourDatabaseName';
  • Note whether is_broker_enabled is 0 and whether the GUID looks inconsistent with other environment records.

Step 2: Reset the Service Broker GUID Using ALTER DATABASE

  • Take the database offline for single-user access to avoid conflicts:
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Force a new Service Broker GUID to be generated and resynchronized with sys.databases:
ALTER DATABASE YourDatabaseName SET NEW_BROKER;
  • If you need to preserve existing conversations/dialogs instead of resetting them, use the error-recovery variant:
ALTER DATABASE YourDatabaseName SET ERROR_BROKER_CONVERSATIONS;

Step 3: Re-Enable Service Broker and Restore Multi-User Access

  • Explicitly enable the broker now that the GUID is aligned:
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
  • Return the database to normal multi-user operation:
ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Check File System and Permissions (Windows Server 2025 / Windows 11)

  • Confirm the SQL Server service account has full read/write permissions on the .mdf and .ldf files located typically at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Verify no leftover lock handles exist from antivirus or backup agents by checking Windows Resource Monitor (resmon.exe) under the “CPU” tab’s associated handles search.

Alternative or Verification Steps

  • Verify the Fix: Re-run the SELECT query from Step 1 and confirm is_broker_enabled now returns 1 and the GUID value is consistent across sys.databases and internal metadata.
  • Alternative Solution: If ALTER DATABASE SET NEW_BROKER fails due to active sessions, detach and re-attach the database using sp_detach_db and CREATE DATABASE ... FOR ATTACH WITH NEW_BROKER to force GUID regeneration at the attach level.