Troubleshooting Guide: SQL Server Error 9658 – Transmission Queue Table Access Failure

Why This Error Happens

Error 9658 typically occurs when the Service Broker transmission queue becomes inaccessible due to insufficient permissions on the system catalog views or when the database’s Service Broker GUID is corrupted or mismatched after a restore/migration. This can also happen if the database is in an inconsistent state (e.g., SUSPECT or RECOVERY_PENDING) or if the sys.transmission_queue table has internal metadata corruption. In some cases, this is triggered by a conflict between the Service Broker endpoint configuration and the certificate used for dialog security.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Permissions

• Open SQL Server Management Studio (SSMS) and connect using an account with sysadmin or VIEW SERVER STATE permissions.
• Run the following query to check Broker status:

  SELECT name, is_broker_enabled, service_broker_guid 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• Ensure the executing login has CONTROL permission on the database; grant it if missing:

  GRANT CONTROL ON DATABASE::YourDatabaseName TO [YourLogin];

Step 2: Repair a Mismatched Service Broker GUID

• If the database was recently restored or attached, the Broker GUID may conflict with the original.
• Run the following command to regenerate a new, unique GUID:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Alternatively, if you need to preserve existing message queues, use:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 3: Check for Database Corruption

• Run a full consistency check to rule out corruption affecting system tables:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for errors specifically related to sys.transmission_queue or allocation errors in system pages.
• If corruption is confirmed, restore the database from the last known good backup rather than attempting a repair, as REPAIR_ALLOW_DATA_LOSS can affect Broker metadata integrity.

Step 4: Restart the SQL Server Service and Endpoint

• On Windows Server 2025, open Services.msc and restart the SQL Server (MSSQLSERVER) service to clear any cached Broker session locks.
• Verify the Broker endpoint is listening correctly:

  SELECT * FROM sys.service_broker_endpoints;

• If the endpoint is stopped, restart it via:

  ALTER SERVICE_MASTER_KEY FORCE REGENERATE;

Alternative or Verification Steps

• Verify the fix: Query SELECT * FROM sys.transmission_queue; again. If the error 9658 no longer appears and rows return (or an empty set returns without error), the queue is accessible.
• Alternative solution: If the issue persists after resetting the Broker, detach the database and re-attach it using sp_attach_db, then re-run ALTER DATABASE SET NEW_BROKER to force a clean metadata rebuild.