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

by

Troubleshooting Guide: SQL Server Error 9723 – Broker Instance ID Duplication

Why This Error Happens

This error occurs when SQL Server Service Broker detects that two or more databases on the same instance share an identical service_broker_guid, preventing the affected database from initializing the broker. This typically happens after restoring or attaching a database from a backup or copied data files without properly regenerating a new broker instance ID. It can also occur after database mirroring failovers, log shipping, or when a database snapshot/copy process duplicates the broker GUID without administrator intervention.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Databases

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following query to list all databases and their broker GUIDs:
SELECT name, database_id, service_broker_guid, is_broker_enabled 
FROM sys.databases;
  • Look for duplicate values in the service_broker_guid column to confirm which databases are conflicting.

Step 2: Generate a New Broker Instance ID

  • Once you’ve identified the duplicate (usually the newly restored or copied database), set it to single-user mode or ensure no active connections exist.
  • Run the NEW_BROKER command to assign a fresh GUID, breaking any conversations but resolving the GUID collision:
ALTER DATABASE [YourDatabaseName] SET NEW_BROKER;
  • If you need to preserve existing message conversations, use ERROR_BROKER_CONVERSATIONS instead, though this will end conversations with an error rather than silently dropping them:
ALTER DATABASE [YourDatabaseName] SET ERROR_BROKER_CONVERSATIONS;

Step 3: Re-enable the Broker

  • After updating the GUID, explicitly re-enable the Service Broker for the database:
ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;
  • Verify that is_broker_enabled now returns 1 for the affected database by re-running the query from Step 1.

Step 4: Restart the SQL Server Service (If Required)

  • If the error persists in the SQL Server Error Log after the ALTER DATABASE commands, restart the SQL Server service via Windows Services (services.msc) or PowerShell:
Restart-Service -Name "MSSQLSERVER" -Force
  • For named instances on Windows Server 2025, ensure you target the correct service name (e.g., MSSQL$INSTANCENAME).

Alternative or Verification Steps

  • Verification: Check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) to confirm Error 9723 no longer appears on startup, and run SELECT service_broker_guid FROM sys.databases; to ensure all GUIDs are now unique.
  • Alternative: If ALTER DATABASE fails due to active sessions, take the database offline first (ALTER DATABASE [DBName] SET OFFLINE WITH ROLLBACK IMMEDIATE;), apply the NEW_BROKER command, and bring it back online (ALTER DATABASE [DBName] SET ONLINE;).