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

by

Troubleshooting Guide: SQL Server Error 9775

“Cannot create a new Service Broker in read-only database “%.*ls”.”

Why This Error Happens

This error occurs when SQL Server attempts to generate a new Service Broker GUID (via ALTER DATABASE SET NEW_BROKER or ENABLE_BROKER operations, or during restore/attach operations) but the database is currently in a READ_ONLY state. Service Broker requires write access to the database to store its identifier and configuration metadata, so any operation triggering broker initialization will fail if the database cannot be written to. This commonly happens after restoring a database from a read-only source, attaching a database that retained read-only flags, or when a database is intentionally set to read-only for reporting/archival purposes but an application or maintenance script still attempts broker-dependent operations.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

  • Open SQL Server Management Studio (SSMS) or a query window connected to your SQL Server 2025 instance.
  • Run the following query to check the read-only status:
SELECT name, is_read_only, is_broker_enabled 
FROM sys.databases 
WHERE name = 'YourDatabaseName';
  • Confirm that is_read_only returns 1, verifying this is the root cause.

Step 2: Set the Database to READ_WRITE Mode

  • Ensure no active connections are using the database, as changing the state requires exclusive access.
  • Run the following T-SQL command, replacing with your database name:
ALTER DATABASE YourDatabaseName SET READ_WRITE WITH ROLLBACK IMMEDIATE;
  • The ROLLBACK IMMEDIATE clause forcibly disconnects existing sessions to apply the change immediately.

Step 3: Enable or Reset the Service Broker

  • With the database now writable, execute the broker command that previously failed:
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
  • If you specifically need a new Broker GUID (common after a database copy/restore causing GUID conflicts), use:
ALTER DATABASE YourDatabaseName SET NEW_BROKER;

Step 4: Revert to Read-Only (If Required by Business Logic)

  • If the database must remain read-only for your environment (e.g., a reporting replica), only re-apply this setting after broker configuration is finalized.
  • Run:
ALTER DATABASE YourDatabaseName SET READ_ONLY;
  • Note: Re-enabling READ_ONLY after this point is safe since the Broker ID is already persisted in the database metadata.

Alternative or Verification Steps

  • Verify the Fix: Re-run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm it returns 1.
  • Alternative Check: If the ALTER DATABASE command hangs during Step 2, check sys.dm_exec_requests for blocking sessions or verify the database isn’t part of an Availability Group where the primary replica dictates read/write access.