Why This Error Happens

Error 9732 during SQL Server startup typically occurs when the Database Engine service account cannot properly initialize one or more system or user database files needed to bring the instance online. This is most commonly caused by NTFS permission changes on the data/log directories, a third-party antivirus or backup agent locking the .mdf/.ldf files, or file-system corruption introduced by an improper shutdown. In rarer cases, it stems from a mismatch between the SQL Server service account and the ACLs applied after a Windows Server 2025 in-place upgrade.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log

• Open C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust instance folder name as needed).
• Look for the line immediately preceding Error 9732 — it usually names the specific file or filegroup that failed to initialize.
• Confirm whether the error references master, tempdb, or a user database.

Step 2: Validate File System Permissions and Locks

• Right-click the referenced .mdf/.ldf/.ndf file(s) → Properties → Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Run icacls "D:\SQLData\*.mdf" /grant "NT SERVICE\MSSQLSERVER":F from an elevated PowerShell/Command Prompt to reset permissions if needed.
• Temporarily disable real-time antivirus scanning or add an exclusion for the SQL data/log directories, since file locks from scanners are a common trigger on Windows Server 2025.

Step 3: Start SQL Server in Minimal Configuration Mode

• Open an elevated Command Prompt and run:
  net start MSSQLSERVER /f /T3608
• This starts the instance with only the master database, allowing you to confirm the service itself can initialize before troubleshooting the specific database file.
• If the service starts successfully in this mode, the issue is isolated to a specific user database file rather than the core engine.

Step 4: Repair or Restore the Affected Database

• If a user database file is at fault, take it offline and run:
  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOLEAD
• If corruption is confirmed, restore the most recent full backup plus transaction log backups rather than relying on REPAIR_ALLOW_DATA_LOSS.
• If master or tempdb is implicated, rebuild system databases using setup.exe /ACTION=REBUILDDATABASE or relocate tempdb files to a healthy drive path via -d, -l, and -e startup parameters in SQL Server Configuration Manager.

Alternative or Verification Steps

• After applying fixes, stop minimal configuration mode and restart the service normally (net start MSSQLSERVER), then confirm the ERRORLOG shows “SQL Server is now ready for client connections” with no further 9732 entries.
• If the error persists, run sqlservr.exe -c -m directly from the Binn directory in an interactive console to capture real-time diagnostic output before escalating to Microsoft Support with the error log attached.

Troubleshooting Guide: Error 9731 – Dialog Security Unavailable for Conversation

Why This Error Happens

This error occurs because Service Broker requires a security certificate bound to the database principal to encrypt and authenticate dialog conversations, but no such certificate exists. It typically arises when a database user or principal was created without an associated certificate, or when the certificate was dropped, expired, or never generated during the initial Service Broker setup. This is common in environments where dialog security (as opposed to transport security) is enforced between endpoints but the certificate infrastructure was not fully configured.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database Principal

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to find the principal ID referenced in the error:

  SELECT name, principal_id FROM sys.database_principals WHERE principal_id = <Id from error>;

• Confirm whether this principal already has a certificate by querying sys.certificates.

Step 2: Create a Certificate for the Principal

• In the context of the correct database, run:

  USE [YourDatabaseName];
  CREATE CERTIFICATE Cert_<PrincipalName>
  AUTHORIZATION [<PrincipalName>]
  WITH SUBJECT = 'Service Broker Dialog Security Certificate';

• Ensure the AUTHORIZATION clause matches the exact database principal referenced in the error message.

Step 3: Exchange Certificates Between Participating Databases (If Cross-Database/Instance)

• Back up the certificate using BACKUP CERTIFICATE Cert_<PrincipalName> TO FILE = 'C:\Certs\Cert_<PrincipalName>.cer';.
• Copy the .cer file to the remote instance/server if the conversation spans multiple databases or servers.
• On the remote side, create a corresponding certificate for the remote principal using CREATE CERTIFICATE ... FROM FILE = 'C:\Certs\...'.

Step 4: Restart the Conversation

• Terminate any stuck or orphaned conversations using END CONVERSATION with the relevant handle, or clean up via sys.conversation_endpoints.
• Re-initiate the BEGIN DIALOG CONVERSATION command from the application or stored procedure to confirm Service Broker can now negotiate security.

Alternative or Verification Steps

• Verify the fix by querying sys.certificates and confirming the certificate is now associated with the correct principal_id; then re-run the Service Broker message send operation to confirm Error 9731 no longer appears.
• If dialog security is not a strict requirement for your scenario, consider switching to transport security only by disabling DIALOG SECURITY at the endpoint level, though this is not recommended for production environments requiring message-level encryption.

Troubleshooting Guide: SQL Server Error 9730

Why This Error Happens

Error 9730 occurs when SQL Server Service Broker attempts to locate a security certificate tied to a database principal, but that principal’s ID no longer maps to a valid server-level principal. This typically happens because the login or user associated with the certificate was dropped, renamed, or orphaned after the Service Broker conversation (dialog) was initiated. It can also occur after a database restore or migration where principal SIDs become mismatched between the database and the server’s master database.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Principal ID

• Run the following query against the affected database to cross-reference the principal ID from the error message:

SELECT * FROM sys.database_principals WHERE principal_id = <Id_from_error>;

• Compare this against sys.server_principals to confirm the mismatch:

SELECT * FROM sys.server_principals WHERE sid = (SELECT sid FROM sys.database_principals WHERE principal_id = <Id_from_error>);

• If no matching row exists in sys.server_principals, the principal is confirmed orphaned.

Step 2: Re-map or Re-create the Server Login

• If the login still exists but the SID is mismatched (common after restore/migration), fix it using ALTER USER:

ALTER USER [UserName] WITH LOGIN = [MatchingLoginName];

• If the login was fully deleted, re-create it with the original SID using:

CREATE LOGIN [LoginName] WITH PASSWORD = 'StrongPassword123!', SID = 0x0123456789ABCDEF;

• You can retrieve the original SID from sys.database_principals if it was captured before the login was dropped.

Step 3: Re-associate the Service Broker Certificate

• Navigate to the user that owns the Service Broker certificate and re-issue the certificate binding:

USE [YourDatabaseName];
ALTER CERTIFICATE [CertName] WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'YourPassword');

• If the certificate was tied specifically to the dropped user, drop and re-create it under the newly fixed principal:

DROP CERTIFICATE [CertName];
CREATE CERTIFICATE [CertName] AUTHORIZATION [FixedUserName]
   WITH SUBJECT = 'Service Broker Certificate';

Step 4: Restart the Service Broker and Clear Conversations

• End any stuck or hung conversation endpoints that triggered the original error:

END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server service via services.msc on Windows Server 2025, or run:

Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• To verify the fix, re-run the query in Step 1—the principal ID should now correctly resolve to an active server principal with no NULL results.
• If the error persists, check the SQL Server Error Log (ERRORLOG file, typically located at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\) for any additional Service Broker (Event ID 9700-series) errors that may indicate a deeper certificate chain corruption requiring a full master key backup/restore.

Troubleshooting Guide: Error 9729 – “There is no cluster proxy route for the URI” in SQL Server 2025

Why This Error Happens

Error 9729 occurs when SQL Server’s internal cluster proxy (used for routing requests between nodes in an Availability Group or Fabric-enabled cluster) cannot resolve the requested URI to a valid endpoint. This typically happens because the proxy routing table has not been populated or has become stale after a node failover, service restart, or configuration change. It can also stem from a misconfigured listener endpoint, a corrupted routing cache, or a mismatch between the URI registered at the HTTP.sys layer and the one SQL Server’s cluster service is attempting to resolve.

Step-by-Step Resolution Guide

Step 1: Verify the Cluster Proxy Service Status

• Open an elevated PowerShell session and run Get-Service -Name "SqlClusterProxy*" to confirm the proxy service is running.
• Check Get-ClusterResource output for any resources in a “Failed” or “Pending” state related to the SQL Server role.
• If the service is stopped, restart it using Restart-Service -Name "SqlClusterProxy" and monitor the SQL Server Error Log for re-registration messages.

Step 2: Rebuild the Routing Table

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ and review the ERRORLOG file for the exact URI referenced in the 9729 message.
• Run ALTER AVAILABILITY GROUP [YourAGName] MODIFY LISTENER with the correct port to force SQL Server to re-register the listener endpoint.
• Clear the stale HTTP.sys reservation using netsh http delete urlacl url=http://+:1433/ (adjust port/protocol as needed), then re-register it with netsh http add urlacl url=http://+:1433/ user="NT SERVICE\MSSQLSERVER".

Step 3: Validate Cluster Network Configuration

• Open Failover Cluster Manager and confirm that all nodes show “Up” status under Networks.
• Check that the cluster IP resource and the SQL Server listener are on the same subnet and that no firewall rule on Windows Server 2025 is blocking the proxy port (default 5022 or custom AG endpoint port).
• Run Test-Cluster to generate a validation report and look for warnings under “Network” and “Inter-node Communication” sections.

Step 4: Restart the SQL Server and Cluster Services in Sequence

• Stop the SQL Server service first: Stop-Service -Name "MSSQLSERVER".
• Stop and restart the Cluster Service: Stop-Service -Name "ClusSvc" followed by Start-Service -Name "ClusSvc".
• Start SQL Server again: Start-Service -Name "MSSQLSERVER" and confirm the proxy route re-registers by checking the ERRORLOG for “Proxy route added” entries.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%ClusterProxy%' to confirm the URI now appears as a registered route.
• If the error persists, remove and re-add the Availability Group listener entirely via SSMS (Always On High Availability > Availability Group Listeners), then re-run netsh http show urlacl to confirm the correct URI is reserved.

Troubleshooting Guide: SQL Server Error 9728

Why This Error Happens

This error occurs when SQL Server Service Broker attempts to locate a security certificate tied to a database principal ID that no longer exists in the sys.database_principals table. This typically happens when a user or role associated with a Service Broker conversation endpoint is dropped from the database while active dialogs or routes still reference that principal’s security context. The mismatch between the cached conversation metadata and the current principal ID lookup table triggers the failure, often surfacing after user cleanup scripts, migrations, or orphaned user remediation tasks.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Principal ID

• Query sys.conversation_endpoints to find active or dying conversations referencing broken principal links.
• Run: SELECT conversation_handle, principal_id FROM sys.conversation_endpoints WHERE is_initiator = 1;
• Cross-reference the returned principal_id against sys.database_principals using: SELECT * FROM sys.database_principals WHERE principal_id = <ID_from_above>;
• If no matching row returns, the principal has been dropped and confirms the root cause.

Step 2: End the Affected Conversations

• Since the backing certificate/principal no longer exists, the conversation cannot be cleanly closed and must be forcibly ended.
• Run: END CONVERSATION '<conversation_handle>' WITH CLEANUP;
• Repeat for each orphaned handle identified in Step 1.
• If WITH CLEANUP fails due to lock contention, restart the SQL Server Service Broker task using ALTER DATABASE [YourDB] SET DISABLE_BROKER; ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Re-provision the Security Certificate and Principal

• Recreate the database user/principal that was dropped, ensuring the same name is used if legacy code references it explicitly.
• Run: CREATE USER [BrokerServiceUser] WITHOUT LOGIN;
• Re-associate and re-issue the certificate binding for Service Broker authentication.
• Run: CREATE CERTIFICATE BrokerCert AUTHORIZATION [BrokerServiceUser] WITH SUBJECT = 'Service Broker Certificate';
• Re-grant CONNECT permissions on the relevant Service Broker endpoint to the newly created principal.

Step 4: Validate Service Broker Configuration

• Check that the is_broker_enabled flag is set to 1 for the database in question.
• Run: SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDB';
• If disabled, re-enable it via ALTER DATABASE [YourDB] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Alternative or Verification Steps

• Verify the fix by re-running the original query/application action that triggered Error 9728; check the SQL Server Error Log (via Event Viewer > Applications and Services Logs > MSSQLSERVER) to confirm Event ID 9728 no longer appears.
• If the error persists after certificate re-provisioning, consider a full Service Broker queue reset by dropping and recreating the affected queue and service objects, as residual metadata corruption in sys.transmission_queue can sometimes survive a simple cleanup.

Troubleshooting Guide: SQL Server Error 9727 — Dialog Security Not Available (No Remote Service Binding)

Why This Error Happens

This error occurs when Service Broker attempts to establish a secure dialog with a remote service, but no REMOTE SERVICE BINDING object exists to define the certificate-based security context for that target service. Service Broker requires an explicit binding that maps a local user (with an associated certificate) to the remote service name in order to encrypt and authenticate the conversation. Without this binding, SQL Server has no way to determine which credentials to use for establishing dialog security, causing the conversation initiation to fail.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker and Certificate Prerequisites

• Confirm Service Broker is enabled on the database: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• Ensure a master key exists in the database: SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
• Verify that a certificate has been created and exchanged with the remote server for authentication purposes.
• Check that the certificate has not expired by querying sys.certificates.

Step 2: Create the Missing Remote Service Binding

• Identify the local database user associated with the certificate used for outbound authentication (e.g., dbo or a dedicated Service Broker user).
• Run the following T-SQL on the initiator database:

CREATE REMOTE SERVICE BINDING [RemoteBindingName]
TO SERVICE 'TargetServiceName'
WITH USER = [LocalUserWithCertificate];

• Replace TargetServiceName with the exact name of the remote Service Broker service (case-sensitive and must match exactly).
• Replace LocalUserWithCertificate with the database user that owns the certificate paired with the remote endpoint.

Step 3: Validate Certificate Trust Between Instances

• Confirm the public key certificate from the remote instance has been imported locally using CREATE CERTIFICATE ... FROM FILE.
• Ensure the certificate on both ends uses matching START_DATE and EXPIRY_DATE values and has not lapsed.
• Check the SQL Server error log on both instances for related Service Broker handshake failures (Event ID 9719 or 9731 often accompany this issue).

Step 4: Restart Service Broker and Re-Test the Conversation

• Run ALTER DATABASE [YourDatabaseName] SET NEW_BROKER; only if conversations are stuck and can be safely reset (use with caution in production).
• Re-send a test message using BEGIN DIALOG CONVERSATION to confirm the binding resolves correctly.
• Monitor sys.transmission_queue for any residual error descriptions tied to the conversation handle.

Alternative or Verification Steps

• Run SELECT * FROM sys.remote_service_bindings; to confirm the new binding appears with the correct service_name and user_id mapping.
• If the error persists, verify firewall rules on Windows Server 2025 / Windows 11 are not blocking the Service Broker endpoint port (default 4022), and confirm the endpoint state is STARTED via SELECT * FROM sys.service_broker_endpoints;.

Troubleshooting Guide: SQL Server Error 9726 — Remote Service Binding Dropped

Why This Error Happens

Error 9726 occurs when SQL Service Broker automatically drops a remote service binding because the certificate or user account associated with it has been dropped or altered, breaking the trust relationship needed for encrypted remote communication. This commonly happens after a master key regeneration, certificate expiration, or when the associated database user is deleted without first removing dependent Service Broker objects. It can also surface during database restores or mirroring failovers where certificate metadata becomes out of sync between the local and remote databases.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Remote Service Binding

• Run the following query to locate the binding ID referenced in the error:
  SELECT * FROM sys.remote_service_bindings WHERE remote_service_binding_id = <%d>;
• Cross-reference the principal_id and service_contract_name to determine which database user and certificate were originally tied to this binding.
• Check SQL Server Error Logs (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact timestamp to correlate with recent certificate or user changes.

Step 2: Verify Certificate and User Integrity

• Confirm the certificate still exists by running:
  SELECT * FROM sys.certificates WHERE name = 'YourCertificateName';
• If the certificate was dropped, re-import it from backup using:
  CREATE CERTIFICATE YourCertificateName FROM FILE = 'C:\Certs\YourCertificateName.cer';
• Ensure the associated database user (mapped to the certificate) still exists using SELECT * FROM sys.database_principals WHERE type = 'C';

Step 3: Recreate the Remote Service Binding

• Drop any residual broken binding reference (if still partially present):
  DROP REMOTE SERVICE BINDING YourBindingName;
• Recreate the binding, pointing to the correct user and remote service name:
  CREATE REMOTE SERVICE BINDING YourBindingName TO SERVICE 'RemoteServiceName' WITH USER = YourCertUser;
• Validate that the SERVICE BROKER GUID on both local and remote databases matches using SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDBName';

Step 4: Restart Service Broker and Validate Endpoint Configuration

• Restart Service Broker on the affected database:
  ALTER DATABASE YourDBName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (only if a full reset is acceptable, as this changes the broker GUID)
• Alternatively, re-enable Broker without resetting GUID:
  ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Check that the TCP endpoint for Service Broker is active on port 4022 (default) via SELECT * FROM sys.tcp_endpoints WHERE type = 4;

Alternative or Verification Steps

• Verify the fix by sending a test message through the Service Broker conversation and confirming it arrives without triggering Error 9726 in the SQL Server Error Log.
• If the issue persists after recreating the binding, check Windows Firewall rules on Windows Server 2025 to ensure port 4022 is not blocked, and confirm the SQL Server service account has appropriate permissions on the certificate’s private key file in C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\.

Troubleshooting Guide: SQL Server Error 9725 – Invalid Schema Dropped from Message Type

Why This Error Happens

This error occurs when SQL Server Service Broker validates an XML schema collection bound to a message type and determines that the schema is malformed, incomplete, or incompatible with the XML parser’s requirements. This typically happens after a schema collection is altered improperly, during an upgrade/migration where schema definitions became corrupted, or when a message type references a schema ID that no longer resolves to a valid, well-formed XML Schema Definition (XSD). Because Service Broker enforces strict validation on message contracts, any ambiguity in the schema (such as unresolved namespaces or broken type references) causes SQL Server to drop it rather than risk message queue corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Message Type and Schema ID

• Run the following query to locate the message type referenced in the error: SELECT name, message_type_id, validation, xml_collection_id FROM sys.service_message_types WHERE message_type_id = <ID from error>;
• Cross-reference xml_collection_id with sys.xml_schema_collections to confirm whether the schema still exists or is NULL.
• Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related warnings preceding Error 9725.

Step 2: Rebuild or Re-import the XML Schema Collection

• Export the original XSD definition from source control or your application repository if available.
• Drop the broken collection safely: DROP XML SCHEMA COLLECTION [SchemaName]; (only after confirming no active queues depend on it).
• Re-create it using a validated XSD: CREATE XML SCHEMA COLLECTION [SchemaName] AS N'<xsd:schema>...</xsd:schema>';
• Rebind the schema collection to the message type: ALTER MESSAGE TYPE [MessageTypeName] VALIDATION = VALID_XML WITH SCHEMA COLLECTION [SchemaName];

Step 3: Check for Service Broker Corruption or Pending Upgrade Issues

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying page-level corruption affecting system catalog views.
• If the database was recently migrated from an older SQL Server version, verify compatibility level using SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; and update it if necessary with ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
• Restart the SQL Server service via Windows Services (services.msc) or PowerShell: Restart-Service -Name 'MSSQLSERVER' to clear any cached metadata related to the broken schema.

Step 4: Apply Latest Cumulative Update

• Confirm your current build number: SELECT @@VERSION;
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as schema validation bugs are frequently patched.
• Reboot Windows Server 2025 / Windows 11 after installation to ensure all binaries are properly replaced.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected Service Broker conversation and confirming it validates against the schema without triggering Error 9725 in the ERRORLOG.
• If the issue persists, set message type validation to WELL_FORMED_XML temporarily as a workaround while you rebuild the full XSD schema collection offline.

Troubleshooting Guide: SQL Server Error 9724 – Activated Procedure Output Message

Why This Error Happens

Error 9724 is technically an informational message rather than a true error; it’s raised by the Service Broker activation engine to surface diagnostic output (such as PRINT statements or RAISERROR calls) generated by an activated stored procedure. This typically occurs when the activated proc contains explicit debugging output, encounters a handled exception that logs a message, or triggers a RAISERROR with a severity level below 11. Because activated procedures run in the background without a client session, SQL Server redirects any output through the SQL Server Error Log using this message code instead of returning it directly to a caller.

Step-by-Step Resolution Guide

Step 1: Locate and Review the Full Error Log Entry

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Alternatively, query the log directly: EXEC sp_readerrorlog 0, 1, 'Message 9724';
• Identify the exact queue name and procedure name (%ls placeholders) referenced in the message to determine the source object.

Step 2: Inspect the Activated Stored Procedure Code

• Use sys.service_queues to find the queue definition: SELECT name, activation_procedure FROM sys.service_queues WHERE name = 'YourQueueName';
• Open the procedure in SSMS and search for PRINT, RAISERROR, or THROW statements that may be generating unintended verbose output.
• On Windows Server 2025, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating timestamps if the log wasn’t fully visible in SSMS.

Step 3: Correct or Suppress Unnecessary Diagnostic Output

• If the output is unintentional debug code left in production, remove or comment out the PRINT/RAISERROR statements and redeploy the stored procedure.
• If the output is intentional (e.g., custom logging), consider redirecting it to a dedicated logging table instead of using PRINT, to avoid cluttering the error log: INSERT INTO dbo.ActivationLog (LogMessage, LogDate) VALUES (@MessageText, GETDATE());

Step 4: Validate Queue Activation Settings

• Confirm the queue isn’t stuck in a retry loop causing repeated log entries: SELECT is_activation_enabled, activation_procedure FROM sys.service_queues WHERE name = 'YourQueueName';
• If the procedure is failing silently and retrying, temporarily disable activation to break the loop: ALTER QUEUE YourQueueName WITH ACTIVATION (STATUS = OFF);
• Re-enable activation once the underlying code logic has been patched: ALTER QUEUE YourQueueName WITH ACTIVATION (STATUS = ON);

Alternative or Verification Steps

• Verify the Fix: After redeploying the corrected procedure, monitor the SQL Server Error Log in real-time for 15-20 minutes to ensure Message 9724 no longer appears for that specific queue/procedure combination.
• Alternative Solution: If you cannot modify the stored procedure immediately, increase the Error Log retention cycle (via sp_cycle_errorlog) to prevent the log file from growing too large while you schedule the permanent code fix.

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;).

Why This Error Happens

This error is generated by SQL Server’s Service Broker component when it attempts to establish a TCP connection to a remote endpoint and fails before a dialog can be initiated. The root cause is typically a network-level blockage—such as a Windows Firewall rule, a disabled Service Broker endpoint, or an incorrect port binding—preventing the TCP handshake from completing. It can also occur if the destination server’s SQL Server instance is offline, the endpoint state is set to STOPPED, or DNS/hostname resolution is failing between the two servers.

Step-by-Step Resolution Guide

Step 1: Verify the Service Broker Endpoint Status

• Open SQL Server Management Studio (SSMS) and connect to the destination instance.
• Run the following query to check the endpoint state:

SELECT name, state_desc, port FROM sys.tcp_endpoints;

• If state_desc shows STOPPED, start it with:

ALTER ENDPOINT [Service_Broker_Endpoint] STATE = STARTED;

Step 2: Check Windows Firewall Rules on Both Servers

• On Windows Server 2025 / Windows 11, open Windows Defender Firewall with Advanced Security.
• Confirm an Inbound Rule exists allowing TCP traffic on the Service Broker port (default is 4022, but verify via the query in Step 1).
• If missing, create a new rule via PowerShell:

New-NetFirewallRule -DisplayName "SQL Server Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow

Step 3: Validate Network Connectivity and DNS Resolution

• From the source server, test raw connectivity to the destination using Test-NetConnection:

Test-NetConnection -ComputerName "DestinationServerName" -Port 4022

• If the test fails, confirm the destination server’s hostname resolves correctly in DNS, or add a manual entry to the local hosts file (C:\Windows\System32\drivers\etc\hosts) as a temporary workaround.

Step 4: Review Certificate and Authentication Configuration

• If using Certificate-based authentication for Service Broker, ensure the certificates on both ends have not expired.
• Run this query on both servers to check expiration dates:

SELECT name, expiry_date FROM sys.certificates;

• Re-issue and re-exchange certificates if they have expired or do not match.

Alternative or Verification Steps

• To verify the fix, check the sys.transmission_queue table on the source database for pending messages—if it clears without new 9722 errors appearing in the SQL Server Error Log, the connection is restored.
• As an alternative, enable Service Broker trace flags (2537) temporarily to capture more granular diagnostic output if the standard error message does not reveal the specific failure reason.

Troubleshooting Guide: Error 9721 – Service Broker Failed to Clean Up Conversation Endpoints

Why This Error Happens

Error 9721 occurs when Service Broker’s internal cleanup task is unable to remove expired or ended conversation endpoints from the sys.conversation_endpoints table due to resource contention or metadata corruption. This is frequently caused by a blocking transaction holding locks on Service Broker system tables, insufficient worker threads, or an underlying I/O failure preventing the write operation. In some cases, this error surfaces as a secondary symptom of database corruption affecting the internal Service Broker catalog views.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Root Cause Details

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for messages immediately preceding or following Error 9721, such as Error 9617 (conversation handle issues) or I/O-related errors (Error 823/824/825).
• Alternatively, query the log directly:

EXEC sys.xp_readerrorlog 0, 1, N'Service Broker';

• Identify the specific database name mentioned in the %.*ls placeholder to scope your troubleshooting.

Step 2: Check for Blocking Sessions on Broker System Tables

• Run the following query to identify sessions blocking Service Broker’s internal cleanup process:

SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource 
FROM sys.dm_exec_requests 
WHERE blocking_session_id <> 0;

• If a long-running transaction is holding locks on sys.conversation_endpoints or sys.transmission_queue, terminate it safely using KILL <session_id>.
• Restart the Service Broker task for the affected database to force re-initialization:

ALTER DATABASE [YourDatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Warning: Only use NEW_BROKER if you understand it will end all existing conversations and assign a new Service Broker GUID.

Step 3: Validate Database Integrity

• Run a full consistency check to rule out corruption in the system catalog affecting Broker metadata:

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

• If corruption is found specifically in Service Broker objects, review the output for repair recommendations (REPAIR_REBUILD vs REPAIR_ALLOW_DATA_LOSS).
• Ensure the SQL Server service account has full read/write permissions on the data and log file directories (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).

Step 4: Increase Worker Thread Availability

• If the error correlates with high CPU load or thread starvation, check current worker thread usage:

SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count 
FROM sys.dm_os_schedulers 
WHERE status = 'VISIBLE ONLINE';

• Adjust the Max Worker Threads setting in SSMS under Server Properties > Processors if the queue count is consistently high, or leave at 0 for automatic configuration based on CPU cores.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to apply thread pool changes.

Alternative or Verification Steps

• Verify the Fix: After applying the steps above, monitor the error log for 24 hours using xp_readerrorlog to confirm Error 9721 does not recur, and check sys.conversation_endpoints to ensure the row count is decreasing as expected rather than growing indefinitely.
• Alternative Solution: If the issue persists despite a clean DBCC CHECKDB, consider disabling and re-enabling Service Broker entirely via ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER; followed by ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER; during a maintenance window to force a full reset of the internal queue state.

Troubleshooting Guide: SQL Server Error 9720

Why This Error Happens

Error 9720 occurs when Service Broker detects that a conversation endpoint references a database that is currently in an inconsistent transitional state—typically because it was just attached or restored. Service Broker requires a stable, fully recovered database context with a valid Service Broker GUID before it can route or deliver messages to endpoints within that database. This mismatch commonly arises when a database is restored with KEEP_EXISTING or a new GUID is assigned without properly updating the Service Broker routing and conversation metadata, leaving orphaned or stale endpoint references.

Step-by-Step Resolution Guide

Step 1: Verify the Service Broker GUID Status

• Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
• Run the following query to check the current broker configuration:

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

• Confirm whether is_broker_enabled is set to 0 or if the GUID appears duplicated across databases (a common cause after attach/restore operations).

Step 2: Re-enable or Reset Service Broker on the Affected Database

• If the database was restored and Service Broker is disabled, enable it using a new GUID to avoid conflicts with the original source database:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• If you need to preserve existing conversations and the source database is offline, use:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Note: NEW_BROKER will end all existing conversations in the database, so use this only if message continuity from the prior state is not required.

Step 3: Clear Orphaned Conversation Endpoints

• Query for stale or orphaned endpoints that may still be pointing to the old database state:

  SELECT * FROM sys.conversation_endpoints 
  WHERE far_service IS NOT NULL AND state = 'ER';

• End any endpoints stuck in an error (ER) or disconnected state manually:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server service via services.msc on Windows Server 2025/Windows 11 to force Service Broker to reinitialize its internal routing table.

Step 4: Validate Windows Firewall and Endpoint Configuration

• Since Service Broker relies on TCP endpoints for routing, confirm that the Service Broker port (default 4022) is not blocked.
• Open Windows Defender Firewall with Advanced Security, navigate to Inbound Rules, and ensure a rule exists allowing traffic on port 4022 for sqlservr.exe.
• Verify the endpoint configuration in SQL Server:

  SELECT name, port, state_desc FROM sys.tcp_endpoints;

Alternative or Verification Steps

• To verify the fix, send a test message between two conversation endpoints and confirm successful delivery using SELECT * FROM sys.transmission_queue;—this table should return no persistent error rows once resolved.
• If the error persists after resetting the broker, consider dropping and recreating the affected Service Broker queue and service objects (DROP QUEUE/DROP SERVICE followed by CREATE QUEUE/CREATE SERVICE) to eliminate any corrupted metadata bindings.

Troubleshooting Guide: SQL Server Error 9719

Why This Error Happens

Error 9719 occurs when Service Broker attempts to process a conversation endpoint, but the database associated with that conversation has been moved, detached, or restored since the conversation was originally established. This happens because the Service Broker route and dialog metadata (stored in sys.conversation_endpoints) becomes orphaned when the underlying database identity changes — typically after a restore, attach, or database mirroring/failover operation alters the database ID or Service Broker GUID. As a result, SQL Server cannot validate that the endpoint still belongs to an active, consistent database context, and it blocks further message processing on that conversation.

Step-by-Step Resolution Guide

Step 1: Verify the Service Broker GUID Consistency

• Run SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; in SQL Server Management Studio (SSMS).
• Compare this GUID against the GUID referenced in sys.conversation_endpoints (farservice, service_id columns) to confirm a mismatch caused by the restore/attach operation.
• Check the SQL Server error log (via Get-Content on C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG in PowerShell) for correlating timestamps of the restore/attach event.

Step 2: Re-enable and Reset Service Broker on the Database

• Open a new query window in SSMS and disable Service Broker temporarily: ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
• Re-enable it with a new broker identity: ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• If the database was restored with WITH KEEP_EXISTING, instead regenerate the GUID explicitly: ALTER DATABASE YourDatabaseName SET NEW_BROKER; (this clears all pending stale conversations safely).

Step 3: Clean Up Orphaned Conversation Endpoints

• Identify stale endpoints using: SELECT conversation_handle, state_desc FROM sys.conversation_endpoints WHERE state_desc = 'DISCONNECTED_INBOUND' OR state_desc = 'DISCONNECTED_OUTBOUND';
• End the orphaned conversations manually: END CONVERSATION 'conversation_handle_value' WITH CLEANUP;
• Restart the SQL Server (MSSQLSERVER) service via services.msc or Restart-Service -Name MSSQLSERVER -Force in an elevated PowerShell session on Windows Server 2025 to clear any cached broker routing tables.

Step 4: Validate File and Permission Integrity Post-Restore

• Confirm the SQL Server service account has full read/write NTFS permissions on the .mdf, .ldf, and .ndf files under their storage path (commonly D:\SQLData\ or C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\).
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption introduced during the attach/restore process that could affect broker metadata.

Alternative or Verification Steps

• After applying fixes, send a test message through the application or run SELECT * FROM sys.transmission_queue; — an empty or decreasing queue confirms conversations are processing normally again.
• If the error persists, as a last resort, drop and recreate the affected Service Broker queues, services, and contracts using CREATE QUEUE, CREATE SERVICE, and CREATE CONTRACT scripts matching your original schema, then re-test message flow.

Troubleshooting Guide: Error 9718 – Service Broker Queue Activation Conflict

Why This Error Happens

This error occurs because SQL Server Service Broker only allows a single activation mechanism per queue, and the target queue already has stored procedure activation enabled. When an attempt is made to create an event notification for QUEUE_ACTIVATION on that same queue, SQL Server rejects the request since the two activation types (internal stored procedure activation and external event notification activation) are mutually exclusive. This typically happens when a developer or deployment script tries to layer a new activation method onto a queue without first checking or removing the existing configuration.

Step-by-Step Resolution Guide

Step 1: Identify the Current Activation Configuration

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to check the queue’s current activation settings:

  SELECT name, is_activation_enabled, activation_procedure 
  FROM sys.service_queues 
  WHERE name = 'YourQueueName';

• Confirm that activation_procedure is populated, indicating stored procedure activation is active.

Step 2: Disable Existing Stored Procedure Activation

• If you intend to use event notifications instead of internal activation, disable the existing procedure-based activation first.
• Execute the following T-SQL command:

  ALTER QUEUE YourQueueName 
  WITH ACTIVATION (STATUS = OFF);

• Verify no active sessions are processing messages against this queue before disabling to avoid message pile-up.

Step 3: Create the Event Notification

• Once activation is disabled, re-run the CREATE EVENT NOTIFICATION statement targeting the QUEUE_ACTIVATION event.
• Example syntax:

  CREATE EVENT NOTIFICATION YourNotificationName 
  ON QUEUE YourQueueName 
  FOR QUEUE_ACTIVATION 
  TO SERVICE 'YourServiceName', 'current database';

• Ensure the target service and route exist prior to execution to prevent secondary binding errors.

Step 4: Restart the Dependent Application Pool or Service

• On Windows Server 2025, restart the SQL Server Agent or associated Windows Service via services.msc if the queue is tied to a scheduled job.
• This clears any cached execution plans referencing the old activation stored procedure.

Alternative or Verification Steps

• Verification: Send a test message to the queue using SEND ON CONVERSATION and monitor the sys.transmission_queue and SQL Server error logs to confirm the event notification fires correctly without the 9718 error.
• Alternative: If reverting to event notifications is not required, simply leave the stored procedure activation intact and skip the CREATE EVENT NOTIFICATION step entirely, as internal activation is generally more efficient for high-throughput Broker queues.

Troubleshooting Guide: Error 9717 – Cannot Enable Stored Procedure Activation on Service Broker Queue

Why This Error Happens

This error occurs because SQL Server Service Broker does not allow a single queue to have both stored procedure activation and event notification for queue_activation configured simultaneously. The queue is designed to use only one activation mechanism at a time to signal that messages have arrived, since both methods are fundamentally different approaches to the same notification task. When an administrator or deployment script attempts to enable ACTIVATION with a stored procedure while an existing CREATE EVENT NOTIFICATION ... ON QUEUE definition is still active on that same queue, SQL Server rejects the change to prevent conflicting activation logic.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Event Notification

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance running on Windows Server 2025 / Windows 11.
• Run the following query to locate the event notification tied to the queue:

SELECT 
    en.name AS EventNotificationName,
    en.event_type_desc,
    o.name AS QueueName
FROM sys.event_notifications en
JOIN sys.objects o ON en.parent_id = o.object_id
WHERE o.name = 'YourQueueName';

• Note the EventNotificationName returned; you will need it to drop the object in the next step.

Step 2: Drop the Existing Event Notification

• Execute a DROP EVENT NOTIFICATION statement using the name retrieved in Step 1.
• Use the exact syntax below in a New Query window in SSMS:

DROP EVENT NOTIFICATION YourEventNotificationName 
ON QUEUE YourSchema.YourQueueName;

• Verify there are no dependent triggers or external applications relying on this specific notification before deletion, as dropping it will stop the “push” based alerts to the Broker service.

Step 3: Enable Stored Procedure Activation

• Once the event notification is removed, re-run the ALTER QUEUE command to attach the stored procedure activation.
• Use the following T-SQL syntax, ensuring the procedure schema is fully qualified:

ALTER QUEUE YourSchema.YourQueueName
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = YourSchema.YourStoredProcedure,
    MAX_QUEUE_READERS = 5,
    EXECUTE AS SELF
);

• Check the Windows Server 2025 Event Viewer (under Applications and Services Logs > MSSQLSERVER) to confirm no errors were logged during the ALTER execution.

Step 4: Restart the SQL Server Service Broker (If Needed)

• If the queue remains in a disabled state after the alteration, toggle the Service Broker status to force a refresh.
• Run ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; followed immediately by ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;.
• Note: This step can cause a brief delay in message processing, so schedule it during a maintenance window.

Alternative or Verification Steps

• Verification: Query sys.service_queues and check the is_activation_enabled column; it should now return 1, confirming the stored procedure activation is active and the conflict is resolved.
• Alternative: If you need to retain the event-driven architecture instead of the stored procedure, simply leave the event notification in place and do not attempt the ALTER QUEUE ... WITH ACTIVATION command, as the two features are mutually exclusive by design.

Troubleshooting Guide: SQL Server Error 9716 — Conversation Group Reference Mismatch

Why This Error Happens

Error 9716 occurs when Service Broker detects an internal inconsistency between the conversation group metadata and the actual conversation handles referencing it, typically caused by abrupt transaction rollbacks, improper END CONVERSATION handling, or corruption in the sys.conversation_endpoints system views. This can also surface after an unclean shutdown, forced failover, or restoring a database with an inconsistent Service Broker state. In most cases, the root cause is orphaned conversation handles that were not properly cleaned up due to interrupted queue processing or disabled Service Broker activation during a critical transaction.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Identify Affected Conversations

• Run SELECT * FROM sys.conversation_endpoints WHERE conversation_handle = '<handle_from_error>'; to confirm the orphaned state.
• Check SELECT is_broker_enabled FROM sys.databases WHERE name = '<YourDatabaseName>'; to ensure Service Broker is active.
• Review the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating timestamps and related 9617/9641 Service Broker warnings.

Step 2: Clean Up Orphaned Conversation Handles

• Execute END CONVERSATION '<conversation_handle>' WITH CLEANUP; for each handle referenced in the error message.
• If multiple handles are affected, script this using a cursor against sys.conversation_endpoints where state = 'CD' (closed) or state_desc = 'DISCONNECTED_INBOUND'.
• Avoid running this during peak transaction hours as it forces an immediate teardown of the conversation group.

Step 3: Rebuild Service Broker Metadata Consistency

• Run DBCC CHECKDB('<YourDatabaseName>') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no underlying page-level corruption exists in the system catalog.
• If corruption is found, restore from the last known good backup via SQL Server Management Studio (SSMS) 21 using Tasks > Restore > Database.
• Alternatively, run ALTER DATABASE <YourDatabaseName> SET NEW_BROKER WITH ROLLBACK IMMEDIATE; only if conversation history is disposable, as this resets the Broker GUID and clears all stale conversation groups.

Step 4: Update SQL Server 2025 Cumulative Patches

• Navigate to Microsoft Update Catalog and verify you are on the latest Cumulative Update (CU) for SQL Server 2025, as early RTM builds had a known race condition in the sys.transmission_queue cleanup routine.
• Install via the standalone setup file and restart the MSSQLSERVER service through services.msc on Windows Server 2025.

Alternative or Verification Steps

• Verify the Fix: Re-run the query against sys.conversation_endpoints for the specific handle; if the result set is empty, the conversation group has been successfully purged.
• Alternative Fallback: If END CONVERSATION ... WITH CLEANUP fails due to a locked transaction, restart the SQL Server instance in single-user mode (sqlservr.exe -m) to force-release the lock on the Broker queue before retrying the cleanup script.

Troubleshooting Guide: SQL Server Error 9713

Why This Error Happens

Error 9713 occurs when a Service Broker message type is linked to an XML schema collection ID that no longer exists in the database’s system catalog. This typically happens after a schema collection is dropped manually while message types still reference it, or due to metadata corruption in the sys.xml_schema_collections system view resulting from an incomplete database restore, replication conflict, or improper database migration. It can also surface after upgrading a database to SQL Server 2025 compatibility level if system catalog consistency checks were skipped during the upgrade process.

Step-by-Step Resolution Guide

Step 1: Identify the Broken Message Type Reference

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to find the orphaned reference:

  SELECT mt.name AS MessageTypeName, mt.xml_schema_collection_id
  FROM sys.service_message_types mt
  LEFT JOIN sys.xml_schema_collections xsc
      ON mt.xml_schema_collection_id = xsc.xml_schema_collection_id
  WHERE mt.xml_schema_collection_id IS NOT NULL
      AND xsc.xml_schema_collection_id IS NULL;

• Note the MessageTypeName and the invalid ID returned, as you will need these for the next step.

Step 2: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no deeper catalog corruption beyond the schema reference.
• If corruption is detected in system tables, restore the database from the last known good backup taken before the error began occurring.
• If DBCC CHECKDB returns clean, proceed to manually repair the reference in Step 3.

Step 3: Recreate or Reassign the XML Schema Collection

• Recreate the missing schema collection using a saved .xsd definition file if available:

  CREATE XML SCHEMA COLLECTION dbo.RestoredSchemaCollection AS N'<xsd content here>';

• Alter the broken message type to point to the valid collection or remove the XML validation requirement entirely:

  ALTER MESSAGE TYPE [YourMessageTypeName]
  VALIDATION = WELL_FORMED_XML;

• Save and execute the script in a maintenance window, as this operation requires exclusive access to the Service Broker objects.

Step 4: Rebuild Service Broker Metadata (If Required)

• If the message type is tied to an active service broker conversation, disable the broker temporarily using ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER;.
• Re-enable it after the schema fix with ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;.
• Restart the SQL Server service via services.msc on Windows Server 2025 or Windows 11 to clear any cached execution plans referencing the old schema ID.

Alternative or Verification Steps

• Re-run the query from Step 1; if it returns zero rows, the invalid reference has been successfully resolved.
• If the error persists after these steps, drop and recreate the specific message type entirely using DROP MESSAGE TYPE followed by a fresh CREATE MESSAGE TYPE statement with a valid, currently existing schema collection.

Troubleshooting Guide: SQL Server Error 9715 — Inconsistent Conversation Endpoint State

Why This Error Happens

Error 9715 occurs when the internal Service Broker metadata for a conversation endpoint becomes corrupted or desynchronized, often due to abrupt shutdowns, storage-level failures, or disk I/O errors that interrupted a transaction log write. This can also result from underlying hardware issues (failing disks, faulty RAM, or unstable storage controllers) that silently corrupt system tables tied to sys.conversation_endpoints. In rarer cases, improper failover handling in an Always On Availability Group can leave Service Broker dialogs in a half-committed state, triggering this inconsistency.

Step-by-Step Resolution Guide

Step 1: Check SQL Server and Windows Event Logs for Hardware Clues

• Open SQL Server Management Studio (SSMS) and review the SQL Server Error Log (Management > SQL Server Logs).
• Open Event Viewer on Windows Server 2025 / Windows 11 and check Windows Logs > System and Application for Disk, NTFS, or Storport errors.
• Look specifically for Event IDs 7, 9, 11, or 51, which indicate disk-level corruption or controller failures.

Step 2: Run DBCC CHECKDB to Identify and Repair Corruption

• Execute the following in a new query window:

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

• If corruption is confirmed, take the database offline for consistency recovery:

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
  ALTER DATABASE YourDatabaseName SET MULTI_USER;

• Always back up the database before running repair commands with REPAIR_ALLOW_DATA_LOSS.

Step 3: End the Problematic Conversation Manually

• If the database check comes back clean but the error persists, manually terminate the broken conversation handle:

  END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• Retrieve the handle GUID from sys.conversation_endpoints using:

  SELECT conversation_handle, state_desc FROM sys.conversation_endpoints WHERE state_desc = 'ERROR';

Step 4: Restart the Service Broker and Validate Configuration

• Disable and re-enable Service Broker on the affected database to flush the internal cache:

  ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Restart the SQL Server (MSSQLSERVER) service via services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 5: Verify Hardware Health at the OS Level

• Run Windows Memory Diagnostic (mdsched.exe) to rule out RAM-related corruption.
• Use chkdsk C: /f /r (or the appropriate drive letter hosting your .mdf/.ldf files) to scan and repair file system-level disk errors.

Alternative or Verification Steps

• Re-run SELECT * FROM sys.conversation_endpoints; to confirm no endpoints remain in an ERROR or inconsistent state.
• If the issue reoccurs frequently, consider restoring the database from the last known good backup and migrating to new storage hardware, as repeated 9715 errors typically point to a failing physical disk.

Troubleshooting Guide: Error 9712 – Invalid Service Contract Reference in Remote Service Binding

Why This Error Happens

Error 9712 occurs when a remote service binding in Service Broker points to a service contract ID that no longer exists or has been dropped from the database. This typically happens after an incomplete schema migration, a manually edited system catalog, or when a DROP CONTRACT statement is executed without first updating or removing dependent remote service bindings. In some cases, database restoration from an inconsistent backup or replication mismatch between msdb and the user database can also leave orphaned references in sys.remote_service_bindings.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Remote Service Binding

• Open SQL Server Management Studio (SSMS) and connect to the affected SQL Server 2025 instance.
• Run the following query to locate the broken binding:

  SELECT rsb.remote_service_binding_id, rsb.name, rsb.service_contract_id
  FROM sys.remote_service_bindings rsb
  LEFT JOIN sys.service_contracts sc
      ON rsb.service_contract_id = sc.service_contract_id
  WHERE sc.service_contract_id IS NULL;

• Note the remote_service_binding_id and name returned, as these are required for the next step.

Step 2: Drop or Recreate the Invalid Binding

• Execute a controlled drop of the invalid binding using its name:

  DROP REMOTE SERVICE BINDING [BindingNameFromStep1];

• If the binding is still required, recreate the missing service contract first:

  CREATE CONTRACT [ContractName] ([MessageType] SENT BY INITIATOR);

• Then recreate the remote service binding referencing the valid contract ID:

  CREATE REMOTE SERVICE BINDING [BindingName]
  TO SERVICE 'ServiceName'
  WITH USER = [UserName];

Step 3: Validate Service Broker Configuration Files

• On Windows Server 2025 / Windows 11, check the SQL Server error log located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Look for additional Service Broker corruption messages (Error 9675 or 9676) that may indicate deeper catalog corruption.
• If corruption is suspected, run DBCC CHECKCATALOG ('YourDatabaseName') WITH NO_INFOMSGS; to confirm system table consistency.

Step 4: Restart Service Broker on the Database

• Disable and re-enable Service Broker to force a clean reload of contract and binding metadata:

  ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER;
  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• Ensure no active transactions are using Service Broker queues before running this step to avoid blocking.

Alternative or Verification Steps

• Re-run the Step 1 query after applying fixes — if no rows are returned, the invalid binding reference has been resolved.
• If the error persists after recreating the binding, restore the msdb and user database from a known good backup taken before the schema change, then reapply only the valid Service Broker objects.

Troubleshooting Guide: SQL Server Error 9710 – Invalid Conversation Group Reference

Why This Error Happens

This error occurs when a Service Broker conversation endpoint points to a conversation group ID that no longer exists in the sys.conversation_groups metadata table, typically due to corruption in the Service Broker internal tables or an interrupted cleanup operation. It can also arise from inconsistent state caused by improper database restores, replication conflicts, or a crash during an active dialog conversation. In most cases, this indicates that the internal referential integrity between sys.conversation_endpoints and sys.conversation_groups has been broken.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to check for underlying corruption.
• Review the output specifically for errors referencing sys.conversation_endpoints or sys.conversation_groups.

Step 2: Identify Orphaned Conversation Endpoints

• Query the broken references using:

  SELECT conversation_handle, conversation_id, is_initiator 
  FROM sys.conversation_endpoints 
  WHERE conversation_group_id NOT IN (SELECT conversation_group_id FROM sys.conversation_groups);

• Note down the conversation_handle values returned, as these are the invalid endpoints causing Error 9710.

Step 3: End the Orphaned Conversations

• For each conversation_handle identified in Step 2, terminate the conversation forcibly:

  END CONVERSATION 'conversation_handle_value' WITH CLEANUP;

• If WITH CLEANUP fails due to the missing group, use END CONVERSATION 'conversation_handle_value' WITH ERROR = 9711 DESCRIPTION = 'Cleanup Invalid Group'; to force removal.

Step 4: Rebuild Broker Instance (If Corruption Persists)

• If errors continue after cleanup, disable and re-enable the Service Broker to reset the internal GUID mapping:

  ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: This clears pending conversation groups and should be performed during a maintenance window on Windows Server 2025 due to potential locking behavior in this build.

Alternative or Verification Steps

• Verify the fix: Re-run the query from Step 2. If it returns zero rows, the orphaned references have been successfully cleared.
• Alternative solution: If the error persists across restarts, restore the Service Broker-enabled database from the last known good backup taken before the corruption event, ensuring ENABLE_BROKER is set post-restore.

Why This Error Happens

Error 9711 occurs when Service Broker’s internal transmission queue holds a reference to a conversation handle that no longer exists in the sys.conversation_endpoints catalog view. This typically happens due to database corruption, an improperly terminated conversation during a failover or crash, or manual manipulation of Service Broker system tables. It can also surface after a database restore or mirroring failover where the transmission queue state became desynchronized from the conversation endpoint metadata.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether Service Broker system tables are corrupted.
• Query SELECT * FROM sys.transmission_queue; and cross-reference the conversation_handle column against SELECT * FROM sys.conversation_endpoints;.
• Note any orphaned conversation IDs reported in the error message for later validation.

Step 2: Disable and Re-enable Service Broker

• Open a query window in SQL Server Management Studio (SSMS) connected to the affected instance on Windows Server 2025.
• Execute:

  ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• This forces SQL Server to rebuild internal Service Broker routing and queue references, often clearing stale transmission queue entries.

Step 3: End the Orphaned Conversations

• For any conversation handles identified in Step 1 that still exist but are invalid, manually terminate them:

  END CONVERSATION 'conversation_id_here' WITH CLEANUP;

• If the handle no longer exists in sys.conversation_endpoints at all, instead clear the transmission queue entry by restarting the SQL Server service via services.msc or:

  Restart-Service -Name MSSQLSERVER -Force

Step 4: Rebuild Service Broker GUID (If Corruption Persists)

• If the error recurs after restart, regenerate the Service Broker identifier:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER;

• Note this orphans all existing conversations permanently, so only use this on non-critical databases or after confirming no in-flight messages are needed.

Step 5: Restore From a Clean Backup (Last Resort)

• If DBCC CHECKDB reports unrepairable corruption in Service Broker metadata, restore the database from the last known good backup taken before the corruption occurred.
• Validate the backup integrity first with RESTORE VERIFYONLY FROM DISK = 'BackupPath.bak'; before proceeding.

Alternative or Verification Steps

• After applying fixes, re-run SELECT * FROM sys.transmission_queue; — it should return no rows referencing invalid conversation handles, and the SQL Server Error Log should no longer log Event ID 9711.
• As an alternative, if the issue is isolated to a specific application’s messaging queue, drop and recreate the affected Service Broker queue and service objects (DROP QUEUE, CREATE QUEUE) rather than performing a full NEW_BROKER reset.

Troubleshooting Guide: SQL Server Error 9709 – Invalid Message Type Reference in Service Broker Queue

Why This Error Happens

Error 9709 occurs when Service Broker encounters a message in a queue that references a message type ID which no longer exists in the sys.service_message_types catalog view. This typically happens due to metadata corruption in the Service Broker internal tables, an incomplete or interrupted DROP/ALTER operation on a message type, or database restoration/replication processes that left queue data out of sync with the current message type definitions. In some cases, this also surfaces after upgrading or migrating databases to SQL Server 2025 where internal Service Broker catalog consistency was not fully validated beforehand.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS to check for broader database corruption beyond Service Broker objects.
• Query SELECT * FROM sys.service_queues WHERE queue_id = <ID from error> to identify the affected queue name and schema.
• Cross-reference the invalid message type ID using SELECT * FROM sys.service_message_types WHERE message_type_id = <ID from error> — if this returns no rows, the message type definition is confirmed missing.

Step 2: Isolate and Drain the Affected Queue

• Disable the queue temporarily to prevent further processing errors:

  ALTER QUEUE [YourQueueName] WITH STATUS = OFF;

• Attempt to view queue contents safely using:

  SELECT * FROM [YourQueueName] WITH (NOLOCK);

• If the queue cannot be read due to the corruption, note the queueid and conversationhandle values for later cleanup via sys.transmission_queue and sys.conversation_endpoints.

Step 3: Rebuild or Restore Service Broker Metadata

• If a recent clean backup exists, restore the database to a point before the corruption occurred, using:

  RESTORE DATABASE [YourDatabaseName] FROM DISK = 'D:\Backups\YourDatabase.bak' WITH REPLACE, RECOVERY;

• If no backup is available, recreate the missing message type with the same ID characteristics is not possible directly (IDs are system-generated), so instead purge the corrupted queue entries:

  ALTER QUEUE [YourQueueName] WITH STATUS = ON, POISON_MESSAGE_HANDLING (STATUS = OFF);

• Manually drain and discard unreadable messages using RECEIVE TOP(1) FROM [YourQueueName]; in a loop within a try/catch block to avoid full queue lockup.

Step 4: Validate and Reinitialize Service Broker

• Verify the Service Broker GUID is still correctly enabled:

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

• If disabled or inconsistent, reset it with:

  ALTER DATABASE [YourDatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Restart the SQL Server service via services.msc on Windows Server 2025 or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated PowerShell/Command Prompt.

Alternative or Verification Steps

• After applying fixes, re-run DBCC CHECKDB and query sys.service_queues alongside sys.service_message_types to confirm no further ID mismatches exist, and verify SQL Server Error Log (via EXEC sp_readerrorlog) no longer shows Error 9709.
• If the issue persists or recurs frequently, consider dropping and recreating the Service Broker service/queue/contract chain entirely from your deployment scripts, then re-enable NEW_BROKER to generate a fresh, consistent GUID and metadata structure.

Troubleshooting Guide: SQL Server Error 9707

Why This Error Happens

Error 9707 occurs when a Service Broker queue references an activation user (via the ACTIVATION clause’s EXECUTE AS setting) whose corresponding database principal has been dropped, renamed, or orphaned from the login mapping. This typically happens after a user account is deleted from the database without first updating or disabling the queue’s activation procedure settings, or when a database is restored/migrated to another server where the Security Identifier (SID) no longer matches an existing login. Since the Queue Monitor cannot resolve a valid security context to execute the stored procedure, it throws this error and disables activation for that queue.

Step-by-Step Resolution Guide

Step 1: Identify the Broken Queue and User ID

• Query the system views to translate the numeric IDs from the error message into readable names.
• Run the following in SQL Server Management Studio (SSMS):

SELECT name, principal_id FROM sys.database_principals WHERE principal_id = <User ID from error>;
SELECT name, activation_procedure, execute_as_principal_id FROM sys.service_queues WHERE object_id = <Queue ID from error>;

• If the first query returns no rows, the principal has been orphaned or dropped, confirming the root cause.

Step 2: Re-map or Recreate the Orphaned User

• If the user exists in sys.server_principals (the login) but not in sys.database_principals, repair the mapping using the modern syntax (avoid the deprecated sp_change_users_login in SQL Server 2025).
• Execute the following to fix the orphaned SID:

ALTER USER [YourUserName] WITH LOGIN = [YourServerLoginName];

• If the user was intentionally deleted, you must create a new user or use an existing valid one to proceed to the next step.

Step 3: Update the Queue’s Activation Context

• Alter the Service Broker queue to point to a valid, existing database user for the EXECUTE AS clause.
• Use the ALTER QUEUE command to reset the security context:

ALTER QUEUE [YourQueueName]
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = [YourActivationProcedure],
    MAX_QUEUE_READERS = 5,
    EXECUTE AS N'ValidUserName'
);

• Ensure the ValidUserName has CONNECT permissions and ownership rights on the target schema.

Step 4: Restart the Queue Monitor

• Toggle the queue status to force the SQL Server Service Broker engine to re-read the updated metadata and clear the cached error state.
• Run:

ALTER QUEUE [YourQueueName] WITH STATUS = OFF;
ALTER QUEUE [YourQueueName] WITH STATUS = ON;

Alternative or Verification Steps

• Verify the Fix: Check the SQL Server Error Log or sys.dm_broker_queue_monitors to confirm the state column shows the queue as RECEIVES_OCCURRING or NOTIFIED rather than an error state.
• Alternative Fix: If permissions errors persist (Error 9674) after fixing 9707, grant the EXECUTE AS user the specific EXECUTE permission on the stored procedure manually via GRANT EXECUTE ON [ProcedureName] TO [ValidUserName];.

Troubleshooting Guide: SQL Server Error 9708 – Invalid Conversation Group Reference

Why This Error Happens

Error 9708 occurs when the Service Broker metadata becomes inconsistent, typically because the internal link between a message in a queue and its associated conversation_group_id has been broken or orphaned. This corruption is most commonly caused by an improperly terminated transaction, an unexpected server shutdown during Service Broker activity, or physical database corruption affecting system tables like sys.conversation_groups or sys.sysxmitqueue. In rarer cases, this can also result from a failed or interrupted database restore/attach operation involving Service Broker-enabled databases.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on your Windows Server 2025/Windows 11 machine.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to check for underlying corruption.
• Review the output for errors specifically related to Service Broker system tables (sys.conversationendpoints, sys.conversationgroups).

Step 2: Identify and Isolate the Orphaned Queue Message

• Query the queue directly using the Queue ID referenced in the error (%d) to locate the specific message: SELECT * FROM sys.transmission_queue WHERE conversation_group_id = 'invalid-guid-here';
• Cross-reference the conversation_handle against sys.conversation_endpoints to confirm the group ID genuinely does not exist.
• Document the service_name and message_type to understand which application or stored procedure is generating this orphaned message.

Step 3: Purge the Invalid Message or Rebuild the Queue

• If the message is confirmed orphaned and non-critical, end the specific conversation using: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• If the queue itself is heavily corrupted, disable Service Broker activation temporarily via ALTER QUEUE [QueueName] WITH STATUS = OFF;, then re-enable it after cleanup with STATUS = ON.
• For severe cases, consider dropping and recreating the specific Service Broker queue and service (ensure you back up message data first if required).

Step 4: Restore from a Clean Backup (If Corruption Persists)

• Navigate to your backup directory (e.g., D:\SQLBackups\) and identify the most recent backup taken prior to the error onset.
• Restore the database to a new instance using RESTORE DATABASE [YourDatabaseName] FROM DISK = 'D:\SQLBackups\YourDatabaseName.bak' WITH REPLACE;
• Re-point application connection strings to the restored database once validated.

Alternative or Verification Steps

• To verify the fix, re-run DBCC CHECKDB and query sys.transmission_queue to ensure no further rows reference invalid conversation_group_id values.
• If the error persists after cleanup, as an alternative, drop and recreate the Service Broker configuration entirely using ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER; after disabling it, which forces a regeneration of internal GUIDs and clears lingering metadata links.

Troubleshooting Guide: SQL Server Error 9706

Why This Error Happens

This error occurs when a Service Broker queue references a stored procedure (set via the ACTIVATION clause) that no longer exists, has been renamed, or was dropped after the queue was created. It can also happen if the procedure exists but under a different schema than what the queue’s activation metadata expects. Additionally, corruption in the system catalog views (sys.objects, sys.service_queues) can cause SQL Server to flag a valid procedure as invalid.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Queue and Procedure IDs

• Run the following query to resolve the IDs mentioned in the error message:

SELECT object_id, name, schema_id 
FROM sys.service_queues 
WHERE object_id = <queue_id>;

• Cross-reference the procedure ID:

SELECT object_id, name 
FROM sys.objects 
WHERE object_id = <procedure_id>;

• If the second query returns no rows, the procedure has been dropped or never existed.

Step 2: Recreate or Restore the Missing Stored Procedure

• Check source control, DACPAC backups, or .sql deployment scripts for the original procedure definition.
• Recreate the procedure using the exact original name and schema:

CREATE PROCEDURE [dbo].[YourProcedureName]
AS
BEGIN
    -- original logic
END;

• Ensure the schema matches what the queue expects (check via sys.service_queues.schema_id).

Step 3: Reassign the Activation Procedure on the Queue

• If the procedure was renamed or moved, update the queue’s activation settings to point to the correct procedure:

ALTER QUEUE [YourQueueName]
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = dbo.NewProcedureName,
    MAX_QUEUE_READERS = 5,
    EXECUTE AS OWNER
);

• Confirm the EXECUTE AS principal has EXECUTE permission on the procedure to avoid a secondary permissions error.

Step 4: Validate Service Broker Configuration

• Open SQL Server Configuration Manager (Windows Server 2025 / Windows 11) and confirm the SQL Server service account has not changed, as this can affect EXECUTE AS context resolution.
• Restart the SQL Server service if activation still fails to trigger:

Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Verify the fix by sending a test message to the queue and checking sys.transmission_queue and SQL Server Error Log for renewed 9706 errors.
• If the error persists despite a valid procedure, disable and re-enable activation (ALTER QUEUE ... WITH ACTIVATION (STATUS = OFF) then STATUS = ON) to force SQL Server to refresh its internal metadata cache for the queue.

Troubleshooting Guide: SQL Server Error 9705

Why This Error Happens

Error 9705 occurs when Service Broker encounters messages in a queue that reference a conversation handle no longer present in the sys.conversation_endpoints catalog view. This typically results from database corruption, an improperly restored or attached database where conversation metadata became desynchronized from the queue’s message table, or manual manipulation of Service Broker system tables/queues. It can also surface after a database mirroring failover or replication scenario where conversation state was not fully synchronized before the queue data was committed.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Corruption

• Run SELECT * FROM sys.conversation_endpoints WHERE conversation_handle = '<handle_from_error>'; to confirm the handle truly does not exist.
• Query the affected queue directly (e.g., SELECT * FROM [YourQueueName]) using WITH (NOLOCK) to avoid further locking issues while inspecting orphaned messages.
• Note the Queue ID from the error message and cross-reference it with SELECT name, object_id FROM sys.service_queues WHERE object_id = <queue_id>;.

Step 2: Run Database Consistency Checks

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying page-level corruption affecting system Service Broker tables.
• If corruption is found specifically in Service Broker internal tables, review the output for recommended REPAIR_ALLOW_DATA_LOSS options, but only as a last resort.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025 default installs) for correlating I/O or corruption warnings around the same timestamp.

Step 3: Purge Orphaned Messages Safely

• Isolate the specific messages referencing the invalid handle using the conversation_handle column in the queue.
• If the queue allows direct RECEIVE operations, drain the specific poison messages into a temporary holding table: RECEIVE TOP(1) * FROM [YourQueueName] INTO #TempHolding WHERE conversation_handle = '<handle_from_error>';
• If RECEIVE fails due to the broken reference, disable the queue temporarily (ALTER QUEUE [YourQueueName] WITH STATUS = OFF;), export remaining valid data, and recreate the queue object if corruption persists.

Step 4: Rebuild Service Broker Identifiers (If Necessary)

• If the conversation metadata remains desynchronized after cleanup, generate a new Service Broker GUID to reset internal routing: ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
• Warning: This command deletes all existing conversations and messages in the database, so only use this on non-production systems or after confirming data loss is acceptable.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (Run as Administrator) to clear any cached broker state.

Alternative or Verification Steps

• After applying fixes, re-run DBCC CHECKDB and query sys.transmission_queue to ensure no further orphaned handles are being generated during normal message traffic.
• If the error persists immediately after a restore operation, restore from an earlier backup taken prior to the Service Broker corruption, as NEW_BROKER resets are destructive and should only be a last resort for non-critical environments.

Microsoft SQL Server 2025 — Error 9701: Cannot Start Service Broker Activation Manager

Why This Error Happens

Error 9701 occurs when the Service Broker activation manager fails to initialize, typically because the SQL Server Database Engine service account lacks the necessary permissions to create or access the internal queue reader worker threads. This is often triggered by a corrupted msdb database, a damaged Service Broker configuration (missing or mismatched service_broker_guid), or insufficient memory/thread resources at startup. In most cases, this error is a secondary symptom — the actual root cause is logged separately in the SQL Server error log or the Windows Application/System event log.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server and Windows Error Logs

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs, or locate the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for entries immediately preceding or following Event ID 9701, especially messages referencing msdb, Broker, or Service Broker Manager.
• Cross-check the Windows Event Viewer (eventvwr.msc) under Windows Logs > Application for related SQL Server (MSSQLSERVER) source errors occurring at the same timestamp.
• Note any accompanying error numbers (e.g., 9692, 9693, or 17053) as these pinpoint the true underlying cause.

Step 2: Verify Service Broker Status and GUID Integrity

• Open a new query window in SSMS and run:

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

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE msdb SET ENABLE_BROKER;

• If the GUID appears corrupted or mismatched (common after a database restore or mirroring failover), run:

  ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Restart the SQL Server (MSSQLSERVER) service afterward to confirm the activation manager initializes correctly.

Step 3: Check Service Account Permissions and Resource Limits

• Confirm the SQL Server service account (visible in SQL Server Configuration Manager) has local “Log on as a service” rights via secpol.msc > Local Policies > User Rights Assignment.
• Verify the account has full control over the SQL data/log directories (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Check available system memory and thread limits — Service Broker activation requires spare worker threads; review sys.dm_os_sys_info for max_workers_count exhaustion.
• If using Windows Server 2025 hardening policies, ensure no Group Policy Object (GPO) is restricting service-level thread creation or named pipe access.

Step 4: Repair or Rebuild the msdb Database (If Corruption Confirmed)

• Stop the SQL Server service and start it in minimal configuration mode using the command line:

  NET START MSSQLSERVER /f /T3608

• Run DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect corruption.
• If corruption is confirmed and no clean backup exists, consider rebuilding system databases using setup.exe /ACTION=REBUILDDATABASE from the SQL Server 2025 installation media, followed by reapplying the latest cumulative update.

Alternative or Verification Steps

• After applying fixes, restart the SQL Server service and confirm Error 9701 no longer appears in the ERRORLOG; also verify Service Broker activity using SELECT * FROM sys.dm_broker_activated_tasks; to ensure tasks are processing.
• If the issue persists, as a temporary workaround, disable Service Broker on the affected user databases to allow the instance to start, then engage Microsoft Support with the full ERRORLOG and Event Viewer export for deeper diagnostics.

Troubleshooting Guide: SQL Server Error 9704 – XML Validation Failure During Service Broker Message Delivery

Why This Error Happens

Error 9704 occurs when SQL Server Service Broker attempts to deliver a message to a target service, but the message body does not conform to the XML schema defined for that message type. This typically happens due to malformed XML generated by the sending application, a mismatch between the message type’s validation setting (WELLFORMEDXML or VALID_XML) and the actual payload, or a corrupted/truncated message during transit. In some cases, encoding mismatches (such as UTF-8 vs UTF-16) between the sender and the XML schema collection can also trigger this validation failure.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Message and Conversation

• Query the sys.transmission_queue system view to locate the specific message causing the failure.
• Run: SELECT * FROM sys.transmission_queue WHERE transmission_status LIKE '%9704%';
• Note the conversation_handle and message_body for further inspection.

Step 2: Validate the Message Body Against the Expected Schema

• Extract the message_body column and cast it to XML to inspect its structure.
• Run: SELECT CAST(message_body AS XML) FROM sys.transmission_queue WHERE conversation_handle = '<handle>';
• Compare the output against the XML Schema Collection bound to the message type using sys.xml_schema_collections.
• Check for unclosed tags, invalid namespaces, or missing required elements.

Step 3: Correct the Message Type Validation Setting (If Needed)

• Open SQL Server Management Studio (SSMS) and navigate to Service Broker > Message Types under your target database.
• Right-click the relevant message type and review its Validation property.
• If the sending application is not producing strict XML, consider changing validation from VALID_XML to WELL_FORMED_XML temporarily using: ALTER MESSAGE TYPE [YourMessageType] VALIDATION = WELL_FORMED_XML;
• Alternatively, fix the application code generating the XML to match the required schema.

Step 4: Clear the Poisoned Message and Reset the Queue

• If the malformed message is stuck and causing queue poisoning, end the affected conversation.
• Run: END CONVERSATION '<conversation_handle>' WITH CLEANUP;
• Restart the Service Broker queue by disabling and re-enabling it: ALTER QUEUE [YourQueueName] WITH STATUS = OFF; then ALTER QUEUE [YourQueueName] WITH STATUS = ON;

Step 5: Restart the SQL Server Service Broker Endpoint

• On Windows Server 2025, open Services.msc and restart the SQL Server (MSSQLSERVER) service to force Service Broker to reinitialize its internal queues.
• Alternatively, run ALTER ENDPOINT commands to toggle the Service Broker endpoint state without a full server restart.

Alternative or Verification Steps

• Verify the fix by sending a test message through the same service and confirming it appears in the target queue without generating a new entry in sys.transmission_queue.
• If the error persists, drop and recreate the XML Schema Collection associated with the message type, ensuring it matches the exact namespace and structure used by the sending application.

Troubleshooting Guide: SQL Server Error 9699 – Memory Allocation Failure for Service Broker Tasks

Why This Error Happens

Error 9699 occurs when SQL Server dynamically detects additional CPUs (often via hot-add CPU operations or VM resource adjustments) and attempts to spin up corresponding Service Broker worker tasks, but the memory allocator cannot reserve sufficient contiguous memory for these new task structures. This is typically caused by memory pressure at the OS level, restrictive “max server memory” configurations that don’t account for the Service Broker overhead, or fragmentation within SQL Server’s memory clerks. In virtualized or cloud environments running Windows Server 2025, this is exacerbated when dynamic memory/CPU hot-add features are enabled without corresponding adjustments to SQL Server’s memory ceiling.

Step-by-Step Resolution Guide

Step 1: Verify Current Memory Configuration

• Open SQL Server Management Studio (SSMS) and run EXEC sp_configure 'max server memory'; to check the current ceiling.
• Compare this value against total physical RAM on the host; ensure at least 10-15% is reserved for the OS and SQL Server’s non-buffer-pool needs (including Service Broker).
• Check Windows Task Manager (Performance tab) or Get-Counter "\Memory\Available MBytes" in PowerShell to confirm available system memory at the time of the error.

Step 2: Adjust Max Server Memory to Accommodate CPU Scaling

• If your environment uses dynamic CPU hot-add (common in Hyper-V or Azure VMs), lower max server memory to leave more headroom, since each added CPU requires additional worker thread and Service Broker task memory.
• Run the following to reconfigure:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory', <new_value_in_MB>;
  RECONFIGURE;

• As a rule of thumb, reserve an additional 1-2 GB per expected hot-added CPU core beyond your baseline calculation.

Step 3: Disable or Audit Hot-Add CPU/Memory Settings

• If CPUs are being added dynamically without a planned restart, verify whether Hyper-V Dynamic Memory or Processor Compatibility settings are triggering unplanned scale events.
• In Hyper-V Manager, check the VM settings under Processor and Memory tabs; disable “Enable Dynamic Memory” if SQL Server is not tuned to handle runtime scaling.
• For physical hosts, check BIOS/UEFI settings for CPU hot-plug support and disable if not explicitly required.

Step 4: Clear Memory Fragmentation via Service Restart

• If the error persists under adequate memory, a restart of the SQL Server service will reinitialize memory clerks cleanly:

  Restart-Service -Name "MSSQLSERVER" -Force

• Schedule this during a maintenance window, as this clears any fragmented allocations tied to the Service Broker’s internal task pool.

Step 5: Review SQL Server Error Log and DMVs for Memory Clerks

• Query sys.dm_os_memory_clerks to identify if the MEMORYCLERK_SQLSERVICEBROKER clerk is consuming disproportionate memory:

  SELECT * FROM sys.dm_os_memory_clerks 
  WHERE type = 'MEMORYCLERK_SQLSERVICEBROKER';

• Cross-reference findings with the SQL Server Error Log (via xp_readerrorlog) for repeated 9699 entries correlating with CPU count changes.

Alternative or Verification Steps

• To verify the fix, monitor the SQL Server Error Log after the next CPU scaling event and confirm Error 9699 no longer appears; also check sys.dm_os_sys_info to validate that cpu_count matches the expected value without triggering allocation failures.
• As an alternative, if hot-add CPU is a business requirement, consider pre-allocating a fixed, higher CPU count at VM/container creation time rather than relying on runtime scaling, which avoids triggering Service Broker’s dynamic task allocation entirely.

Troubleshooting Guide: Error 9697 – Could Not Start Service Broker

Why This Error Happens

Error 9697 typically occurs when SQL Server cannot initialize the Service Broker endpoint for a specific database, often due to a corrupted or mismatched Service Broker GUID in the database metadata. This can also happen if the master key or certificate used for Service Broker encryption is missing, inaccessible, or if there is a conflict with another database sharing the same Service Broker identifier (common after a database restore or copy operation). Additionally, insufficient permissions on the service account or network connectivity issues on the Service Broker port can prevent proper initialization.

Step-by-Step Resolution Guide

Step 1: Check the SQL Server Error Log for Root Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for messages immediately preceding or following Error 9697, such as missing certificate errors, GUID conflicts, or permission denials.
• Alternatively, review the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Verify and Repair the Service Broker GUID

• Run the following query to check the current Broker GUID and status:

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

• If the database was restored or copied and now has a duplicate GUID, regenerate it using:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Note: Use NEW_BROKER only if you do not need to preserve existing message queues, as this clears them.

Step 3: Validate the Database Master Key and Certificates

• Ensure the Service Master Key (SMK) and Database Master Key (DMK) are accessible and not corrupted.
• Run the following to check for the master key:

  SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

• If missing or corrupted, restore it from backup or recreate it using:

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

Step 4: Confirm Service Account Permissions and Firewall Rules

• Verify that the SQL Server service account has the necessary permissions on the data and log file directories (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Check Windows Firewall settings on Windows Server 2025 to ensure the Service Broker port (default 4022) is not blocked.
• Use PowerShell to verify the rule: Get-NetFirewallRule -DisplayName "*SQL Server Service Broker*".

Step 5: Restart SQL Server Service and Re-enable Broker

• Restart the SQL Server (MSSQLSERVER) service via services.msc or PowerShell: Restart-Service -Name "MSSQLSERVER".
• Re-enable Service Broker manually if it is disabled:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Alternative or Verification Steps

• Verify the fix by querying sys.databases again to confirm is_broker_enabled returns 1 and no further 9697 errors appear in the error log after restarting the service.
• If the issue persists, consider detaching and re-attaching the database, or restoring from a known good backup where Service Broker was functioning correctly.

Troubleshooting Guide: SQL Server Error 9698 — Cannot Start Service Broker Security Manager

Why This Error Happens

This error occurs when SQL Server’s Service Broker component fails to initialize its security manager during startup, typically due to a corrupted or inaccessible master key, certificate issues tied to the Service Broker endpoint, or problems with the underlying cryptographic provider (such as Windows CNG or the local machine key store). It can also result from insufficient permissions on the SQL Server service account to access certificate stores or registry keys required for encryption operations. Because this error is a generic wrapper, the actual root cause is almost always logged separately in the SQL Server error log or the Windows Application/System event logs immediately before or after this entry.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server and Windows Event Logs

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs, or locate the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Cross-reference timestamps with Windows Event Viewer under Windows Logs > Application and Windows Logs > System.
• Look for related errors such as 9693 (certificate errors), 15466 (master key issues), or CNG/cryptographic provider failures that occurred just before Error 9698.

Step 2: Verify the Service Master Key and Database Master Key

• Open a new query window in SSMS connected to the affected instance.
• Run the following to check master key status:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';

• If the key is corrupted or missing, restore it from a backup using:

  RESTORE SERVICE MASTER KEY FROM FILE = 'C:\Backups\ServiceMasterKey.key' 
  DECRYPTION BY PASSWORD = 'YourPassword';

• If no backup exists, you may need to regenerate it, understanding this will require re-encrypting dependent objects.

Step 3: Check SQL Server Service Account Permissions

• Open Services.msc on Windows Server 2025/Windows 11 and confirm the account running the SQL Server service (e.g., NT Service\MSSQLSERVER).
• Ensure this account has Full Control or at least Read access to:
• C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
• The local machine certificate store (certlm.msc > Personal > Certificates)
• Grant permissions via right-click > Properties > Security on the relevant certificate or key file, adding the service account explicitly.

Step 4: Validate the Service Broker Configuration

• Run the following query to confirm Service Broker is enabled on the database:

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

• If disabled unexpectedly or showing inconsistent GUIDs, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Restart the SQL Server service afterward to apply changes cleanly.

Step 5: Restart SQL Server Services and Dependent Components

• Restart the SQL Server instance via Services.msc or using PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Monitor the ERRORLOG in real-time during startup to confirm Error 9698 no longer appears.

Alternative or Verification Steps

• To verify the fix, query sys.dm_broker_connections and sys.transmission_queue to confirm Service Broker is actively processing without errors.
• If the issue persists after certificate and permission fixes, consider running DBCC CHECKDB on the master database to rule out system-level corruption, or rebuild the Service Master Key as a last resort using ALTER SERVICE MASTER KEY REGENERATE (note: this requires re-encrypting all dependent keys and certificates).

Troubleshooting Guide: SQL Server Error 9695 – Service Broker Task Manager Memory Allocation Failure

Why This Error Happens

Error 9695 occurs when SQL Server’s Service Broker component cannot allocate sufficient memory to initialize its internal task manager during startup or runtime. This is typically not a direct memory shortage but a symptom of memory pressure caused by misconfigured max server memory settings, resource contention from other processes on Windows Server 2025/Windows 11, or exhaustion of the SQL Server memory pool due to a memory leak in another subsystem. In some cases, it can also stem from fragmented virtual address space or insufficient system-level memory (physical RAM or page file) available to the SQL Server process.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Preceding Messages

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs, or locate the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for messages logged immediately before Error 9695, such as Out of Memory (OOM) conditions, Error 701, or Error 17300.
• Note the timestamp to correlate with Windows Event Viewer logs under Windows Logs > System and Application.

Step 2: Audit and Adjust SQL Server Memory Configuration

• Run the following query to check current memory settings:

  EXEC sp_configure 'max server memory';

• Ensure max server memory is not set too restrictively (leave at least 2-4 GB free for the OS, more on servers with multiple instances).
• Reconfigure using:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory', 16384; -- adjust value in MB based on available RAM
  RECONFIGURE;

• Restart the SQL Server service via services.msc or SQLServerManager17.msc for changes to take full effect.

Step 3: Check for Memory Pressure from External Processes

• Open Task Manager or Resource Monitor on Windows Server 2025/Windows 11 to identify processes consuming excessive RAM alongside sqlservr.exe.
• Check the page file configuration under System Properties > Advanced > Performance Settings > Advanced > Virtual Memory; ensure it is not set to a fixed, undersized value.
• If running SQL Server inside a container or VM, verify the host has not throttled memory allocation (check Hyper-V Dynamic Memory settings if applicable).

Step 4: Validate Service Broker Configuration Integrity

• Run the following to confirm Service Broker is enabled and not corrupted on the affected database:

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

• If disabled unexpectedly or showing inconsistent state, re-enable it:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Restart the SQL Server service to reinitialize the Service Broker task manager cleanly.

Step 5: Apply Latest Cumulative Update for SQL Server 2025

• Check your current build number:

  SELECT @@VERSION;

• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as known memory allocation bugs in Service Broker are frequently patched.
• Reboot the Windows Server 2025/Windows 11 host after installation to clear any residual memory fragmentation.

Alternative or Verification Steps

• After applying fixes, monitor the ERRORLOG for 24-48 hours to confirm Error 9695 does not recur, and use Performance Monitor (perfmon) with the SQLServer:Memory Manager counter set to validate stable memory usage.
• If the issue persists, as a temporary workaround, disable Service Broker on non-critical databases (ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;) to isolate whether the issue is broker-specific or indicative of broader instance-level memory exhaustion.

Troubleshooting Guide: SQL Server Error 9696 — Service Broker Primary Event Handler Failure

Why This Error Happens

Error 9696 occurs when the SQL Server Service Broker cannot initialize its primary event handler thread during database startup or recovery. This is typically a downstream symptom of a more fundamental issue, such as corrupted Service Broker metadata, a GUID conflict in the service_broker_guid for the database, insufficient worker threads available in the SQL Server scheduler pool, or failure to bind to the TCP endpoint configured for Service Broker communication. Because the error explicitly states it is a symptom rather than a root cause, the actual trigger is almost always logged separately in the SQL Server Error Log immediately before or after this message.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for the Underlying Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs, or locate the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for entries timestamped immediately before Error 9696, looking specifically for Error 9642 (endpoint binding failure), Error 9675 (orphaned conversation handles), or memory allocation failures.
• Run the following query to check Service Broker status at the database level:

SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases;

Step 2: Verify and Resolve Service Broker GUID Conflicts

• A duplicate service_broker_guid across databases (common after a database restore or copy) prevents the broker from starting correctly.
• If a conflict is found, regenerate the GUID using:

ALTER DATABASE [YourDatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Alternatively, if you need to preserve existing conversations, use SET ENABLE_BROKER instead of NEW_BROKER only after confirming no GUID collision exists.

Step 3: Check Firewall and Endpoint Configuration (Windows Server 2025 / Windows 11)

• Confirm the Service Broker endpoint port (default 4022) is not blocked or already in use by another process.
• Open Windows Defender Firewall with Advanced Security and ensure an inbound rule exists allowing TCP traffic on port 4022.
• Run this command in an elevated PowerShell session to check for port conflicts:

Get-NetTCPConnection -LocalPort 4022

• If another process holds the port, either terminate that process or reconfigure the Service Broker endpoint:

ALTER ENDPOINT [ServiceBrokerEndpoint] STATE = STARTED;

Step 4: Increase Worker Thread Availability

• Service Broker requires available worker threads from the SQL Server pool; exhaustion under heavy load can trigger Error 9696.
• Check current thread usage:

SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE';

• If thread starvation is evident, increase the max worker threads setting via sp_configure or SSMS Server Properties > Processors tab, then restart the SQL Server service (Restart-Service MSSQLSERVER in PowerShell).

Step 5: Restart the SQL Server Service and Validate Broker State

• Restart the SQL Server service cleanly using Services.msc or PowerShell:

Restart-Service -Name 'MSSQLSERVER' -Force

• Immediately re-check the error log for recurrence of Error 9696 after the service comes back online.

Alternative or Verification Steps

• Confirm resolution by running SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; — a value of 1 confirms Service Broker is active and the handler started successfully.
• If the issue persists after all steps, consider dropping and recreating the Service Broker queues and services within the affected database, or restoring from a known-good backup where Service Broker was functioning correctly.

Troubleshooting Guide: Error 9694 – Service Broker Manager Could Not Start

Why This Error Happens

Error 9694 occurs when SQL Server’s Service Broker manager fails to initialize during database startup, typically because the Service Broker GUID is missing, duplicated, or corrupted in the sys.databases catalog. This can also happen if the master key used for Service Broker encryption is inaccessible, the endpoint configuration is invalid, or a TCP port conflict prevents the Broker listener from binding. Additionally, file-level corruption in the database or a mismatch after a restore/attach operation (where the broker GUID collides with another database) is a common trigger.

Step-by-Step Resolution Guide

Step 1: Check the SQL Server Error Log and Windows Event Viewer

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for accompanying errors near Error 9694, such as 9692 (GUID conflict) or 15466 (master key issues).
• Cross-reference with Windows Event Viewer under Windows Logs > Application for OS-level network or socket errors.

Step 2: Verify and Reset the Service Broker GUID

• Run the following query to check the current Broker GUID and status:

  SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases;

• If the database was restored or copied and shows a GUID conflict, reset it using:

  ALTER DATABASE [YourDatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Alternatively, if you want to preserve existing conversations, use SET ENABLE_BROKER instead of NEW_BROKER.

Step 3: Check for Port Conflicts on the Service Broker Endpoint

• Identify the TCP port assigned to the Service Broker endpoint (default is 4022):

  SELECT name, port FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';

• Use netstat -ano | findstr "4022" in an elevated PowerShell/Command Prompt on Windows Server 2025 to confirm no other process is binding to that port.
• If a conflict exists, either stop the conflicting process or reassign the Broker endpoint to a different port via ALTER ENDPOINT.

Step 4: Validate the Service Master Key and Database Master Key

• Ensure the Service Master Key is intact by running:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• If the key is corrupted or missing, regenerate it using:

  ALTER SERVICE MASTER KEY FORCE REGENERATION;

• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after regeneration.

Step 5: Restart SQL Server and Monitor Startup

• Restart the SQL Server (MSSQLSERVER) service through the Services console on Windows Server 2025/Windows 11.
• Immediately check the error log again for confirmation that Service Broker initialized without the 9694 error.
• If the issue persists, run SQL Server in a command-line startup trace mode (sqlservr.exe -c -T3608) to isolate the exact failure point.

Alternative or Verification Steps

• Confirm the fix by querying SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; — a value of 1 confirms Broker is active and running.
• If the primary steps fail, consider restoring the database from a known-good backup prior to the corruption event, then re-apply the NEW_BROKER command to ensure a clean GUID state.

Troubleshooting Guide: SQL Server Error 9693 — Endpoint Cannot Listen for Connections

Why This Error Happens

Error 9693 occurs when a SQL Server endpoint (typically TCP, Database Mirroring, or Service Broker) fails to bind to its configured port during startup. This is most commonly caused by another process or SQL Server instance already occupying the specified port, insufficient permissions for the SQL Server service account to bind to the network interface, or a conflicting firewall/network configuration on Windows Server 2025. In some cases, a corrupted or misconfigured endpoint definition in the system catalog can also trigger this failure.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Port or Process

• Open Command Prompt as Administrator and run netstat -ano | findstr <port_number> (e.g., 5022 for mirroring, 1433 for default TCP).
• Note the PID returned and cross-reference it in Task Manager under the “Details” tab.
• Check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact port and error text referenced in the %.*ls message.

Step 2: Verify and Correct Endpoint Configuration

• Open SQL Server Management Studio (SSMS) and run:

  SELECT name, port, state_desc FROM sys.tcp_endpoints;

• If the port is incorrect or duplicated, alter the endpoint:

  ALTER ENDPOINT [Mirroring] STATE = STARTED AS TCP (LISTENER_PORT = 5023);

• Ensure no other SQL instance or application (like IIS or another database engine) is bound to the same port.

Step 3: Check Service Account Permissions and Firewall Rules

• Confirm the SQL Server service account has “Log on as a service” rights via secpol.msc > Local Policies > User Rights Assignment.
• Open Windows Defender Firewall with Advanced Security and verify an inbound rule exists for the SQL Server port:

  New-NetFirewallRule -DisplayName "SQL Server Endpoint" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow

• Restart the SQL Server service via services.msc or PowerShell:

  Restart-Service -Name "MSSQLSERVER" -Force

Step 4: Rebuild or Recreate the Endpoint (If Corrupted)

• If the endpoint metadata is corrupted, drop and recreate it:

  DROP ENDPOINT [Mirroring];
  CREATE ENDPOINT [Mirroring]
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022)
  FOR DATABASE_MIRRORING (ROLE = ALL);

• Restart the SQL Server service and monitor the ERRORLOG for successful endpoint initialization.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_tcp_listener_states; to confirm the endpoint is in a “Ready” state with the correct IP and port bound.
• If the issue persists, temporarily disable third-party antivirus/firewall software to rule out network-layer interference, then re-test connectivity using Test-NetConnection -ComputerName localhost -Port 5022.

Troubleshooting Guide: SQL Server Error 9691 – Endpoint Stopped Listening

Why This Error Happens

Error 9691 occurs when a SQL Server endpoint (such as the Database Mirroring, Service Broker, or TCP endpoint referenced by %S_MSG) encounters an unrecoverable network-level failure and is forced to shut down its listener. This is typically caused by the underlying TCP port being seized by another process, a bound network interface becoming unavailable, exhaustion of system socket resources, or corruption in the SQL Server network configuration. It can also occur if the service account lacks sufficient permissions to bind to the specified port or IP address after a Windows Server 2025 security policy update.

Step-by-Step Resolution Guide

Step 1: Identify the Failed Endpoint and Port

• Check the SQL Server Error Log for the full message text to identify which endpoint triggered the error (e.g., Mirroring, Service Broker, or a custom TCP endpoint).
• Run the following query to list all endpoints and their current state:

  SELECT name, protocol_desc, type_desc, state_desc, port 
  FROM sys.tcp_endpoints;

• Note the port number associated with the failed endpoint for the next step.

Step 2: Check for Port Conflicts

• Open Command Prompt as Administrator and run:

  netstat -ano | findstr <PORT_NUMBER>

• Identify the PID using the port and cross-reference it in Task Manager (Details tab) to see if a non-SQL process has claimed it.
• If a conflict exists, either stop the conflicting process or reassign the SQL Server endpoint to a new port using:

  ALTER ENDPOINT [EndpointName] AS TCP (LISTENER_PORT = 5099);

Step 3: Verify Service Account Permissions

• Open Local Security Policy (secpol.msc) on Windows Server 2025 / Windows 11.
• Navigate to Security Settings > Local Policies > User Rights Assignment.
• Confirm the SQL Server service account has the “Access this computer from the network” right and is not restricted by the “Deny access to this computer from the network” policy.
• Restart the SQL Server service via services.msc to reload permission changes.

Step 4: Restart the Endpoint and Validate Firewall Rules

• Manually restart the endpoint using T-SQL:

  ALTER ENDPOINT [EndpointName] STATE = STARTED;

• Open Windows Defender Firewall with Advanced Security and ensure an inbound rule exists allowing traffic on the SQL Server port (default 1433, or custom mirroring/broker ports like 5022/4022).
• Add a new rule if missing:

  netsh advfirewall firewall add rule name="SQLServerEndpoint" dir=in action=allow protocol=TCP localport=5022

Step 5: Check for Corrupted Network Configuration

• If the issue persists, open SQL Server Configuration Manager.
• Navigate to SQL Server Network Configuration > Protocols for [Instance] and verify TCP/IP is enabled with correct IP binding (check the IPAll section for the correct TCP Port value).
• Restart the SQL Server service to apply changes and clear any cached network binding corruption.

Alternative or Verification Steps

• To verify the fix, re-run SELECT name, state_desc FROM sys.tcp_endpoints; and confirm the state shows STARTED rather than STOPPED.
• If the endpoint repeatedly fails to start, as a workaround, drop and recreate the endpoint entirely using CREATE ENDPOINT with explicit port and IP bindings to clear any residual corrupted metadata.

Troubleshooting Guide: SQL Server Error 9692 — Endpoint Cannot Listen on Port

Why This Error Happens

This error occurs when the TCP port that a SQL Server endpoint (such as the Database Engine, Service Broker, or availability group mirroring endpoint) is trying to bind to is already occupied by another process or service. Port conflicts commonly arise when multiple SQL Server instances, a previous SQL Server process that didn’t shut down cleanly, or another application (like IIS, Windows services, or third-party software) are competing for the same port. Additionally, stale TCP connections left in a TIME_WAIT state or incorrect endpoint configuration during failover cluster setup can also trigger this conflict on Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Identify the Process Using the Conflicting Port

• Open an elevated Command Prompt or PowerShell window.
• Run netstat -ano | findstr :<port> (replace <port> with the port number from the error message, e.g., 5022 or 1433).
• Note the PID (Process ID) returned in the last column.
• Cross-reference the PID using tasklist /FI "PID eq <PID>" to identify the owning application.

Step 2: Resolve the Port Conflict

• If the conflicting process is a stale SQL Server instance, stop it via SQL Server Configuration Manager or run net stop MSSQLSERVER (or the named instance equivalent).
• If another application legitimately needs that port, change the SQL Server endpoint’s port instead:
• Open SQL Server Management Studio (SSMS).
• Run ALTER ENDPOINT [EndpointName] AS TCP (LISTENER_PORT = <new_port>); for the affected endpoint (e.g., Database Mirroring or Service Broker).
• For the Database Engine’s default TCP port, open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for [InstanceName] → TCP/IP → update the TCP Port field under IPAll.

Step 3: Clear Stale TCP Reservations

• Run netsh int ipv4 show excludedportrange protocol=tcp to check if the port falls within a reserved dynamic port range.
• If it does, either exclude the port manually using netsh int ipv4 add excludedportrange protocol=tcp startport=<port> numberofports=1 store=persistent, or choose an endpoint port outside the OS-reserved range.
• Restart the Windows Server 2025 / Windows 11 machine if a lingering TIME_WAIT state persists beyond the standard timeout.

Step 4: Restart the SQL Server Service

• After reconfiguring the port, restart the SQL Server service via SQL Server Configuration Manager or net start MSSQLSERVER.
• Confirm no errors are logged in the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) during startup.

Alternative or Verification Steps

• Verify the fix by running netstat -ano | findstr :<port> again and confirming only the sqlservr.exe process owns the port, then check Event Viewer → Application Log to ensure Event ID 9692 no longer appears.
• If the conflict persists, consider binding the endpoint to a specific IP address instead of all IPs (0.0.0.0) using the LISTENER_IP parameter in ALTER ENDPOINT, which can resolve conflicts in multi-NIC or clustered environments.

Troubleshooting Guide: SQL Server Error 9689 — Service Broker Manager Has Shut Down

Why This Error Happens

Error 9689 occurs when the Service Broker manager encounters a fatal internal error and is forced to shut down, typically due to resource exhaustion, a corrupted Service Broker queue or conversation state, or an inability to allocate memory for message processing. This can also be triggered by orphaned or inconsistent Service Broker metadata left behind after an improper database detach, failover, or restore operation. In some cases, third-party antivirus or endpoint protection software interferes with the SQL Server process threads responsible for Broker communication, causing an abrupt shutdown.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Root Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Alternatively, review the raw log file located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for accompanying errors (such as 9675, 9693, or memory allocation failures) that occurred immediately before Error 9689 — these often reveal the true trigger.

Step 2: Restart the Service Broker Task Manager

• Run the following T-SQL command to disable and re-enable Broker on the affected database, forcing a clean restart of the manager:

ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER;
GO
ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;
GO

• If the database is in a multi-user production environment, schedule this during a maintenance window since it briefly interrupts active conversations.

Step 3: Check for Orphaned or Corrupted Conversation Endpoints

• Query the Broker system views to identify stuck or inconsistent conversations:

SELECT * FROM sys.conversation_endpoints WHERE state NOT IN ('CO');
SELECT * FROM sys.transmission_queue;

• End any problematic conversations manually using:

END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Clear the transmission queue backlog if messages are failing to deliver, as a full queue can exhaust memory and cause the manager to crash.

Step 4: Validate Memory and Resource Allocation

• Open Performance Monitor (perfmon.exe) on Windows Server 2025 and check the SQLServer:Broker Statistics counter group for abnormal spikes.
• Confirm SQL Server’s Max Server Memory setting (under Server Properties > Memory in SSMS) isn’t too restrictive for Broker workload, especially if running alongside other memory-intensive services.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to fully reinitialize the Broker manager if corruption is suspected.

Step 5: Apply the Latest SQL Server 2025 Cumulative Update

• Check your current build version using SELECT @@VERSION;.
• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as several Broker-related memory leak fixes are routinely patched in CUs.
• Reboot the Windows Server 2025 host after installation to ensure all service dependencies reload cleanly.

Alternative or Verification Steps

• After applying fixes, verify Broker is active and stable by running SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; and confirming it returns 1, then monitor the error log for 24–48 hours to ensure Error 9689 does not recur.
• If the issue persists, as an alternative, back up the database, drop and recreate the Service Broker GUID using ALTER DATABASE [YourDatabaseName] SET NEW_BROKER; (note: this breaks existing conversations) to eliminate deep-rooted metadata corruption.

Troubleshooting Guide: SQL Server Error 9690 — “%S_MSG endpoint is now listening for connections”

Why This Error Happens

Error 9690 is actually an informational event, not a failure message — it indicates that a SQL Server endpoint (such as TSQL, Service Broker, Database Mirroring, or HTTP endpoints) has successfully started listening on its configured port. However, this message often gets flagged as a “symptom” when it appears unexpectedly during startup, repeats excessively in the error log, or appears alongside connectivity issues, which usually points to endpoint misconfiguration, port conflicts with another listener, or an endpoint being re-initialized due to a failed previous binding attempt. In Windows Server 2025 and Windows 11 environments, this can also surface when SQL Server 2025’s network layer re-binds after a Windows Firewall or Hyper-V virtual switch change alters available network interfaces.

Step-by-Step Resolution Guide

Step 1: Identify Which Endpoint Triggered the Message

• Open SQL Server Error Log via SSMS (Management > SQL Server Logs) or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Locate the full message text to identify the %S_MSG placeholder value (e.g., “TSQL”, “Database Mirroring”, or “Service Broker”).
• Cross-reference the port number listed in the log with sys.dm_tcp_listener_states by running:

  SELECT * FROM sys.dm_tcp_listener_states;

Step 2: Check for Port Conflicts

• Run netstat -ano | findstr <port_number> in an elevated Command Prompt to see if another process is competing for the same port.
• If a conflict exists, open SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER > TCP/IP, and reassign the static port under the IPAll section.
• Restart the SQL Server service after changing the port: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Validate Windows Firewall and Network Binding

• Go to Windows Defender Firewall with Advanced Security and confirm an inbound rule exists for sqlservr.exe and the specific TCP port.
• If using Hyper-V or a virtual switch on Windows Server 2025, verify the network adapter binding order hasn’t changed, which can cause SQL Server to re-listen on a different IP.
• Run New-NetFirewallRule -DisplayName "SQL Server 2025" -Direction Inbound -Program "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -Action Allow in PowerShell (elevated) if the rule is missing.

Step 4: Review Endpoint Configuration in SQL Server

• Query existing endpoints to check for duplicates or misconfigured states:

  SELECT name, protocol_desc, type_desc, state_desc FROM sys.endpoints;

• Drop and recreate any endpoint stuck in a “Stopped” or conflicting state using DROP ENDPOINT followed by CREATE ENDPOINT with the correct port and protocol.

Alternative or Verification Steps

• Confirm the fix by restarting the SQL Server service and checking that Error 9690 appears once per endpoint in the log without repeating or being followed by connection failure errors (like Error 17182 or 10048).
• As an alternative, use sqlcmd -S <ServerName>,<Port> -U sa -P <password> to manually test connectivity to the specific endpoint and confirm it responds without timeout.

Why This Error Happens

Event ID 9688 is logged when the SQL Server Service Broker manager successfully initializes during database startup or recovery, and it is typically an informational message rather than a true failure. However, it is sometimes reported alongside connectivity issues when Service Broker endpoints fail to establish dialogs due to certificate mismatches, disabled TCP endpoints, or inconsistent ENABLE_BROKER settings on the database. In Windows Server 2025/Windows 11 environments, this can also surface repeatedly in the SQL Server error log if the Broker is restarting due to transport-level errors or firewall blocks on the configured Broker port (default 4022).

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status on the Database

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to confirm Broker is enabled:

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

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDBName SET ENABLE_BROKER;

Step 2: Check the Service Broker Endpoint Configuration

• Confirm the TCP endpoint used by Service Broker is active:

  SELECT name, protocol_desc, state_desc, port FROM sys.tcp_endpoints;

• Ensure the endpoint state is STARTED and the port (commonly 4022) matches your firewall rules.
• If the endpoint is stopped, restart it manually:

  ALTER ENDPOINT [Service_Broker_Endpoint] STATE = STARTED;

Step 3: Validate Windows Firewall and Network Rules

• Open Windows Defender Firewall with Advanced Security on Windows Server 2025/Windows 11.
• Create or verify an inbound rule allowing TCP traffic on port 4022 for the SQL Server service account.
• Confirm no conflicting rules are blocking the Service Broker port across subnets or domain trust boundaries.

Step 4: Review SQL Server Error Logs for Repeated Restarts

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust instance version/path as needed).
• Search for repeated instances of Event ID 9688 combined with transport errors (e.g., 9642 or 9667), which indicate the Broker is cycling due to certificate or master key issues.
• If certificate errors appear, regenerate the Service Broker certificate using CREATE CERTIFICATE and re-associate it with the endpoint.

Step 5: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025/Windows 11.
• Restart the SQL Server (MSSQLSERVER) service to force Service Broker to reinitialize cleanly.
• Monitor the error log immediately after restart to confirm Event ID 9688 appears only once without subsequent transport failures.

Alternative or Verification Steps

• Run SELECT * FROM sys.conversation_endpoints; to confirm active dialogs are being created without errors after the restart.
• If issues persist, use DBCC CHECKDB to rule out database corruption affecting the Broker’s internal queues and services.

Troubleshooting Guide: SQL Server Error 9686 — Conversation Endpoint Dropped

Why This Error Happens

This error occurs when SQL Service Broker automatically terminates a conversation endpoint because it exceeded the configured LIFETIME value or encountered an unrecoverable error during message exchange. It can also be triggered by corrupted dialog security credentials, network interruptions between broker endpoints, or when the receiving queue is disabled and messages back up past the retry threshold. In clustered or Always On environments, a failover mid-conversation can also cause Service Broker to drop the handle to maintain transactional consistency.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Queue Health

• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDB'; to confirm Service Broker is active.
• Check SELECT * FROM sys.transmission_queue; for stuck or poison messages.
• Ensure the target queue is not disabled: SELECT name, is_receive_enabled FROM sys.service_queues;.

Step 2: Inspect and Clean Up Orphaned Conversations

• Query SELECT conversation_handle, state, lifetime FROM sys.conversation_endpoints; to locate expired or error-state conversations.
• If a handle is in ER (error) or DI (disconnected) state, end it manually with END CONVERSATION 'conversation_handle' WITH CLEANUP;.
• Restart affected services if cleanup does not clear the state: open Services.msc on Windows Server 2025 and restart SQL Server (MSSQLSERVER).

Step 3: Review Network and Endpoint Configuration

• Confirm the Service Broker endpoint is listening: SELECT name, state_desc, port FROM sys.tcp_endpoints;.
• Check Windows Firewall rules on both source and destination servers (default Service Broker port is 4022) via Windows Defender Firewall with Advanced Security.
• Validate certificates used for dialog security haven’t expired: SELECT name, expiry_date FROM sys.certificates;.

Step 4: Increase Conversation Lifetime and Enable Error Logging

• If conversations are timing out prematurely, adjust the LIFETIME parameter in your BEGIN DIALOG CONVERSATION statement to a longer duration (e.g., '00:30:00').
• Enable Service Broker trace flags (2551) temporarily for deeper diagnostic logging via DBCC TRACEON (2551, -1);.
• Review the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating entries around the Error 9686 timestamp.

Alternative or Verification Steps

• To verify the fix, re-run the application workflow that triggers Service Broker messaging and monitor sys.conversation_endpoints to confirm new handles remain in CO (conversing) state without dropping.
• If the issue persists, consider rebuilding the Service Broker GUID with ALTER DATABASE YourDB SET NEW_BROKER; (note: this invalidates all existing conversations, use only in non-production or during a maintenance window).

Troubleshooting Guide: SQL Server Error 9687 – Conversation Group Dropped

Why This Error Happens

Error 9687 is raised by SQL Server’s Service Broker when a conversation group is automatically dropped because all conversations within that group have ended and no related messages remain in the queue. This typically occurs as part of normal Service Broker cleanup, but it can surface unexpectedly when queue monitors or activation stored procedures attempt to reference a conversation handle that no longer exists. In environments running SQL Server 2025 on Windows Server 2025 or Windows 11, this is often triggered by orphaned conversation endpoints left behind after an application crash, a poorly designed END CONVERSATION logic, or corrupted Service Broker metadata in the msdb or user database.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to confirm Service Broker is enabled on the database:

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

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

Step 2: Inspect and Clean Orphaned Conversations

• Query active conversation endpoints to identify stale or orphaned conversations:

  SELECT * FROM sys.conversation_endpoints WHERE state_desc = 'DISCONNECTED_INBOUND';

• End any orphaned conversations manually using:

  END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• This prevents the error from recurring due to dangling references in the activation procedure.

Step 3: Review and Correct Activation Stored Procedures

• Open the stored procedure assigned to the queue (check via sys.service_queues) and ensure it includes proper error handling for GET CONVERSATION GROUP.
• Add a TRY...CATCH block around the RECEIVE statement to gracefully handle cases where the conversation group has already been dropped:

  BEGIN TRY
      WAITFOR (RECEIVE TOP(1) * FROM YourQueue), TIMEOUT 5000;
  END TRY
  BEGIN CATCH
      PRINT ERROR_MESSAGE();
  END CATCH

Step 4: Rebuild Service Broker Metadata (If Corruption Is Suspected)

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ on Windows Server 2025/Windows 11 and check the ERRORLOG for related Broker GUID mismatches.
• If corruption is confirmed, run DBCC CHECKDB against the affected database, and if necessary, regenerate the Service Broker GUID:

  ALTER DATABASE [YourDatabaseName] SET NEW_BROKER;

• Note: This step breaks existing conversations permanently, so use only as a last resort.

Step 5: Restart SQL Server Service

• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart to flush any cached Broker session states.

Alternative or Verification Steps

• To verify the fix, re-run your Service Broker application workflow and monitor sys.transmission_queue and the SQL Server ERRORLOG to confirm Error 9687 no longer appears.
• If the issue persists, consider exporting and reinitializing the Service Broker service definitions (CONTRACT, MESSAGE TYPE, and SERVICE objects) in a maintenance window to rule out schema-level corruption.

Troubleshooting Guide: SQL Server Error 9685 – Service Contract Dropped

Why This Error Happens

Error 9685 occurs when a Service Broker service contract referenced by an application or stored procedure has been dropped from the database, either intentionally during a schema change or accidentally due to an incomplete deployment script. This typically happens when a DROP CONTRACT statement executes without properly updating dependent services, queues, or message types that still reference the contract ID. It can also surface after a database restore or migration where contract metadata becomes out of sync with the Service Broker catalog views.

Step-by-Step Resolution Guide

Step 1: Identify the Dropped Contract and Dependent Objects

• Query sys.service_contracts and sys.service_contract_usages to check if the contract ID referenced in the error still exists.
• Run the following to cross-reference affected services:

  SELECT * FROM sys.services WHERE service_id IN (
      SELECT service_id FROM sys.service_contract_usages WHERE contract_id = <ID>
  );

• Check SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact timestamp and contract ID referenced.

Step 2: Recreate the Missing Service Contract

• If the contract was dropped unintentionally, recreate it using the original definition:

  CREATE CONTRACT [YourContractName]
  ([YourMessageType] SENT BY INITIATOR);

• Ensure the message types referenced in the contract still exist in sys.service_message_types.
• Re-bind the contract to the relevant service using ALTER SERVICE if needed.

Step 3: Validate Service Broker Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Confirm Service Broker is enabled on the database:

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

• If disabled, enable it with:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

Step 4: Restore from Backup if Contract Cannot Be Recreated Manually

• If the original contract definition is unknown or complex, restore the database schema from a pre-change backup using SSMS or RESTORE DATABASE in a staging environment.
• Export the contract and related Service Broker objects using the Generate Scripts wizard in SSMS before reapplying to production.
• Apply the extracted CREATE CONTRACT script to the live database during a maintenance window.

Step 5: Audit Deployment Scripts and Permissions

• Review any DevOps or CI/CD pipeline scripts (e.g., Azure DevOps, GitHub Actions) that may have issued an unintended DROP CONTRACT command.
• Verify that only accounts with ALTER ANY CONTRACT permission can modify Service Broker objects:

  SELECT * FROM sys.database_permissions WHERE permission_name = 'ALTER ANY CONTRACT';

Alternative or Verification Steps

• After recreating the contract, send a test message through the affected service using BEGIN DIALOG CONVERSATION and confirm no Error 9685 appears in the SQL Server logs or Windows Event Viewer (Event ID 9685 under Applications and Services Logs > MSSQLSERVER).
• If the issue persists, consider rebuilding the Service Broker GUID using ALTER DATABASE SET NEW_BROKER (note: this breaks existing conversations, so use only in non-production or during planned downtime).

Troubleshooting Guide: SQL Server Error 9683 — Missing Service Referenced by Conversation Group

Why This Error Happens

This error occurs when a Service Broker conversation group references a service that has been dropped or renamed while active conversations or messages still point to it in the sys.services catalog. It typically happens due to improper cleanup sequences (dropping a service before ending its associated conversations) or metadata corruption following an interrupted transaction, failover, or restore operation. In some cases, this also surfaces after a database migration where service IDs were not properly remapped between source and destination SQL Server 2025 instances.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Conversation Group

• Run the following query to locate the conversation group and its associated missing service ID:
• SELECT conversation_group_id, conversation_handle, service_id, far_service FROM sys.conversation_endpoints WHERE service_id = <ID from error message>;
• Cross-reference the ID against sys.services to confirm the service no longer exists:
• SELECT * FROM sys.services WHERE service_id = <ID from error message>;

Step 2: End the Orphaned Conversations Safely

• For each conversation_handle returned in Step 1, terminate it with an error to avoid hanging transactions:
• END CONVERSATION '<conversation_handle>' WITH ERROR = 9683 DESCRIPTION = 'Service removed - forced cleanup';
• If END CONVERSATION fails due to a locked state, move the messages to a backup table before cleanup:
• SELECT * INTO BrokerBackup_Msgs FROM sys.transmission_queue WHERE service_name = '<old_service_name>';

Step 3: Recreate or Remap the Missing Service

• If the service was accidentally dropped, recreate it using the same name referenced in the original queue to restore conversation integrity:
• CREATE SERVICE [<OriginalServiceName>] ON QUEUE [<AssociatedQueueName>] ([<ContractName>]);
• Verify the new service ID now matches active conversation groups by re-running the query from Step 1.

Step 4: Clean Up Residual Conversation Groups

• If recreating the service is not an option (e.g., it was intentionally decommissioned), purge the orphaned groups directly from the queue to prevent the engine from re-throwing Error 9683 on restart:
• ALTER QUEUE [<QueueName>] WITH STATUS = OFF;
• DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• ALTER QUEUE [<QueueName>] WITH STATUS = ON;

Alternative or Verification Steps

• Verification: Check the SQL Server Error Log (via EXEC sp_readerrorlog or Event Viewer on Windows Server 2025) to confirm Error 9683 no longer triggers during the next automatic Broker cleanup cycle.
• Alternative: If the conversation group remains corrupted and blocks database operations, use ALTER DATABASE [YourDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE; to reset the Broker GUID and clear all unresolved conversation metadata (Note: this is a destructive action that invalidates all existing dialogs).

Troubleshooting Guide: SQL Server Error 9684 — “The Service With ID %d Has Been Dropped”

Why This Error Happens

Error 9684 typically occurs when SQL Server’s Service Broker subsystem removes a service definition because its associated queue or contract has been dropped or corrupted, often during a database restore, replication reconfiguration, or manual DDL cleanup. This can also happen when internal metadata in sys.services or sys.service_queues becomes inconsistent due to improper transaction rollback or abrupt server shutdown. In some cases, insufficient permissions on the msdb or application database prevent Service Broker from properly re-registering the service during startup.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Metadata

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check if Service Broker is enabled on the database:

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

• If is_broker_enabled returns 0, enable it:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Inspect and Recreate the Dropped Service

• Query the system views to confirm the service and queue no longer exist:

  SELECT * FROM sys.services;
  SELECT * FROM sys.service_queues;

• If the service was unintentionally dropped, recreate it along with its queue and contract:

  CREATE QUEUE YourQueueName;
  CREATE SERVICE YourServiceName ON QUEUE YourQueueName ([YourContractName]);

• Ensure the contract and message types referenced still exist in the database schema.

Step 3: Check Permissions and Ownership

• Confirm that the service account running SQL Server has CONTROL permission on the affected database.
• In Windows Server 2025 / Windows 11, open Services.msc, locate the SQL Server service, and verify it is running under a valid domain or managed service account.
• Grant explicit permissions if needed:

  GRANT CONTROL ON SERVICE::YourServiceName TO [YourLogin];

Step 4: Restart SQL Server and Monitor Logs

• Restart the SQL Server service via SQL Server Configuration Manager to force Service Broker to reinitialize.
• Open the SQL Server Error Log (Management > SQL Server Logs in SSMS) and search for recurring Event ID 9684 entries to confirm resolution.

Alternative or Verification Steps

• Run SELECT * FROM sys.transmission_queue; to confirm there are no orphaned messages referencing the dropped service — this table should return no rows tied to the missing service ID.
• If the issue persists, restore the affected database from a known-good backup taken before the service was dropped, then re-apply any transactional changes to avoid repeated metadata corruption.

Troubleshooting Guide: SQL Server Error 9681 – Missing Service Contract Reference

Why This Error Happens

This error occurs when Service Broker attempts to validate a conversation endpoint, but the service contract ID referenced by that conversation no longer exists in the sys.service_contracts catalog view. This typically happens when a contract was dropped manually (via DROP CONTRACT) while active conversations or dialogs were still bound to it, or due to metadata corruption in the Service Broker system tables. It can also surface after an incomplete database restore or replication process where Service Broker objects were not synchronized consistently with the message/queue data.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Conversation Endpoint

• Run the following query to locate the broken endpoint using the conversation handle from the error message:
• SELECT conversation_handle, is_initiator, far_service, service_contract_id FROM sys.conversation_endpoints WHERE conversation_handle = '<ID from error>';
• Cross-reference service_contract_id against sys.service_contracts to confirm the contract is indeed missing.

Step 2: Check for Orphaned Service Broker Metadata

• Query sys.service_contracts and sys.services to determine if the contract was dropped while dependencies (like sys.service_contract_usages) still exist.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying catalog corruption affecting Service Broker tables.
• If corruption is found, note the recommended repair level before proceeding (avoid REPAIR_ALLOW_DATA_LOSS unless absolutely necessary).

Step 3: End the Broken Conversation Safely

• Since the contract cannot be restored (it was dropped), the corrupted/orphaned conversation must be terminated to clear the Broker queue.
• Execute: END CONVERSATION '<conversation_handle>' WITH CLEANUP;
• If the standard END CONVERSATION command fails due to the missing contract reference, use the WITH CLEANUP option specifically, as it forces removal of local Service Broker metadata without requiring a handshake with the remote endpoint.

Step 4: Recreate the Service Contract (If Required)

• If the application logic still requires this contract for future messaging, recreate it using the original Transact-SQL script:
• CREATE CONTRACT [YourContractName] ([YourMessageType] SENT BY INITIATOR);
• Re-associate the relevant Service Broker service using ALTER SERVICE to bind the new contract to the existing service queue.

Step 5: Restart the SQL Server Service

• On Windows Server 2025 / Windows 11, open Services.msc and restart the SQL Server (MSSQLSERVER) service to flush the Service Broker cache.
• Alternatively, run ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER; GO ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER; to reset the broker instance ID without a full service restart.

Alternative or Verification Steps

• Verify the Fix: Re-run the initial SELECT query against sys.conversation_endpoints; the specific conversation handle that generated Error 9681 should no longer appear in the active endpoints list.
• Alternative Solution: If END CONVERSATION WITH CLEANUP fails due to a severely corrupted queue, move the data from the affected queue to a new queue using ALTER QUEUE ... WITH STATUS = OFF, then drop and recreate the queue entirely before re-enabling the Service Broker.

Troubleshooting Guide: SQL Server Error 9682 – Missing Service Reference

Why This Error Happens

Error 9682 occurs when the Service Broker metadata becomes inconsistent, typically because a service was dropped (via DROP SERVICE) while conversation endpoints still referenced it in sys.conversation_endpoints. This desynchronization often happens after an improper database restore, a replication/mirroring failover, or a crash during a DDL operation that didn’t fully commit the service drop across all internal catalog tables. In most cases, the underlying cause is orphaned Service Broker metadata rather than a disk-level corruption issue.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Conversation Endpoints

• Run the following query to locate the conversation ID referenced in the error message:
• SELECT conversation_handle, service_id, far_service, state_desc FROM sys.conversation_endpoints WHERE service_id = <missing_id>;
• Cross-reference the service_id against sys.services to confirm the service no longer exists.
• Note the conversation_handle value(s) for cleanup in the next step.

Step 2: Terminate the Broken Conversations

• End the orphaned conversations using the handle(s) identified above:
• END CONVERSATION '<conversation_handle>' WITH CLEANUP;
• If the standard END CONVERSATION fails due to the missing service reference, use the WITH CLEANUP option, which forces removal of endpoint data even if the far-end service is gone.
• Repeat for every orphaned handle returned by the Step 1 query.

Step 3: Run DBCC CHECKDB to Validate Catalog Consistency

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no further Service Broker catalog corruption exists.
• If CHECKDB reports Service Broker-specific catalog errors, a REPAIR_ALLOW_DATA_LOSS may be required, but only after taking a full backup.

Step 4: Rebuild the Missing Service (If Still Required)

• If the service was dropped unintentionally and is still needed by application logic, recreate it:
• CREATE SERVICE [ServiceName] ON QUEUE [SchemaName].[QueueName] ([ContractName]);
• Ensure the service_id aligns with application expectations by checking dependent stored procedures or Broker activation code.

Step 5: Restart the SQL Server Service (Windows Server 2025 / Windows 11)

• Open Services.msc, locate SQL Server (MSSQLSERVER) or the named instance, and restart it to clear any cached Broker metadata in memory.
• Alternatively, restart via PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Verify the fix by re-running the Step 1 query — it should return zero rows for the previously missing service_id.
• If errors persist after cleanup, enable Service Broker trace flags (2537, 2540) temporarily and review the SQL Server Error Log for deeper diagnostic messages related to Broker queue/service synchronization.

Troubleshooting Guide: SQL Server Error 9680 — Missing Conversation Group

Why This Error Happens

Error 9680 occurs when the internal Service Broker metadata becomes desynchronized, typically because the sys.conversation_endpoints table references a conversation_group_id that no longer exists in sys.conversation_groups. This is most commonly caused by improper database restores, replication/mirroring failovers that didn’t fully synchronize Service Broker queues, or abrupt termination of transactions during conversation cleanup. In rarer cases, it stems from corruption in the system catalog views due to disk-level issues or an unexpected shutdown while Service Broker transmission queues were active.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of the Corruption

• Open SSMS and run SELECT * FROM sys.conversation_endpoints WHERE conversation_group_id NOT IN (SELECT conversation_group_id FROM sys.conversation_groups);
• Note the conversation_handle and service_name values returned, as these identify the orphaned endpoints causing Error 9680.
• Check SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for repeated occurrences correlating with failover or restore events.

Step 2: End the Orphaned Conversations

• For each conversation_handle identified in Step 1, run END CONVERSATION 'handle-guid-here' WITH CLEANUP; to force-remove the broken endpoint without waiting for the remote peer acknowledgment.
• If the WITH CLEANUP option fails due to active transactions, first run ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; to clear blocking sessions, then retry the command.
• Switch the database back using ALTER DATABASE [YourDB] SET MULTI_USER; once cleanup is confirmed.

Step 3: Rebuild Service Broker Metadata

• Disable and re-enable Service Broker on the affected database to force a metadata refresh:
• ALTER DATABASE [YourDB] SET DISABLE_BROKER;
• ALTER DATABASE [YourDB] SET ENABLE_BROKER;
• If the GUID conflict persists (common after a database copy/restore), generate a new Service Broker identifier using ALTER DATABASE [YourDB] SET NEW_BROKER; (Note: this will end all existing conversations, so only use this on non-production or fully tested environments).

Step 4: Apply Latest SQL Server 2025 Cumulative Update

• Navigate to the Microsoft Update Catalog and confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, as this specific metadata race condition has partial fixes in recent builds.
• Install via the standalone patch or through SQL Server Setup > Maintenance > Edition Upgrade to ensure Windows Server 2025 compatibility binaries are current.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after patching.

Alternative or Verification Steps

• Re-run the diagnostic query from Step 1; if it returns zero rows, the orphaned reference has been successfully cleared and Error 9680 should no longer populate in the Application Event Log.
• If the error persists specifically after an Always On Availability Group failover, consider manually dropping and recreating the affected Service Broker queue and service objects (DROP QUEUE, DROP SERVICE, then recreate via CREATE QUEUE/CREATE SERVICE) as a last-resort alternative to a full broker reset.

Troubleshooting Guide: SQL Server Error 9678 – Service Broker Missing Contract Reference

Why This Error Happens

This error occurs when a Service Broker service object references a contract ID that no longer exists in the sys.service_contracts system catalog, typically due to an incomplete or interrupted DROP CONTRACT operation. It can also arise from metadata corruption in the msdb or user database system tables, or from a botched database restore/migration where contract definitions were not copied consistently with their dependent services. In some cases, manual manipulation of system catalog views or a failed Service Broker configuration script leaves orphaned references behind.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Service and Contract IDs

• Run the following query to cross-reference the IDs from the error message:

SELECT service_id, name FROM sys.services WHERE service_id = <%d from error>;
SELECT service_contract_id, service_contract_id 
FROM sys.service_contract_usages 
WHERE service_id = <%d from error>;

• Confirm whether the contract ID exists in sys.service_contracts:

SELECT * FROM sys.service_contracts WHERE service_contract_id = <%d from error>;

Step 2: Run Database Consistency Checks

• Execute DBCC CHECKDB against the affected database to detect catalog corruption:

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

• Review the output for Service Broker–specific catalog inconsistencies (msg 9678 or related 9667–9675 range errors).
• If corruption is confirmed, plan for REPAIR_ALLOW_DATA_LOSS only as a last resort after taking a full backup.

Step 3: Drop and Recreate the Orphaned Service

• Since the contract is missing, the broken service must be dropped and redefined with valid contract bindings:

DROP SERVICE [BrokenServiceName];

• Recreate the service with a valid, existing contract reference:

CREATE SERVICE [BrokenServiceName]
ON QUEUE [YourQueueName]
([ValidContractName]);

• Validate that the contract exists before binding:

SELECT name FROM sys.service_contracts;

Step 4: Restore From a Known Good Backup (If Metadata Is Unrecoverable)

• If system catalog corruption is severe, restore the msdb or user database from the last verified clean backup taken prior to the error.
• Use Windows Server 2025 File History or SQL Server native backup files located at the default path:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\
• Reapply any transaction logs up to the point just before the corruption occurred.

Alternative or Verification Steps

• After recreating the service, re-run the triggering Service Broker conversation or application process and confirm Error 9678 no longer appears in the SQL Server Error Log (Management Studio > SQL Server Logs) or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• As an alternative, if the service is no longer needed, simply drop the orphaned service entirely with DROP SERVICE rather than recreating it, eliminating the broken reference without restoring data.

Troubleshooting Guide: SQL Server Error 9679

Why This Error Happens

Error 9679 occurs when the Service Broker metadata in a SQL Server database becomes inconsistent, specifically when a service object (sys.services) references a service queue ID that no longer exists in sys.service_queues. This is typically caused by catalog corruption resulting from an improper database restore, a failed replication/mirroring synchronization, manual manipulation of system catalogs, or an unexpected shutdown during a Service Broker DDL operation (such as CREATE SERVICE, ALTER SERVICE, or DROP QUEUE). Because Service Broker relies on tightly linked internal metadata tables, any desynchronization between a service and its underlying queue triggers this integrity violation.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to determine if the corruption is isolated to Service Broker objects or indicates broader database damage.
• Query SELECT * FROM sys.services WHERE service_queue_id NOT IN (SELECT object_id FROM sys.service_queues); to identify the exact orphaned service ID and missing queue ID reported in the error.
• Check the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the timestamp of when the inconsistency first appeared.

Step 2: Disable Service Broker Temporarily (If Safe)

• Take the database offline to application traffic that depends on Service Broker messaging.
• Execute ALTER DATABASE YourDatabaseName SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE; to halt further broker activity and prevent queue read/write conflicts while you investigate.
• Verify the broker status using SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';.

Step 3: Recreate the Missing Queue or Drop the Orphaned Service

• If the queue was intentionally dropped, recreate it using CREATE QUEUE dbo.RecoveredQueue; and then bind it to the orphaned service via ALTER SERVICE [YourServiceName] ON QUEUE dbo.RecoveredQueue;.
• If the service itself is obsolete and no longer needed, remove it entirely using DROP SERVICE [YourServiceName]; to eliminate the dangling reference.
• For severe catalog corruption that ALTER/DROP commands cannot resolve, run DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS) only as a last resort, and only after taking a full backup.

Step 4: Restore From a Clean Backup (If Repair Fails)

• Identify the last known-good full backup taken before the corruption timestamp found in Step 1.
• Restore the database to a new instance name first (e.g., YourDatabaseName_Recovery) to validate integrity using RESTORE DATABASE... WITH RECOVERY.
• Run DBCC CHECKDB against the restored copy to confirm no Service Broker inconsistencies exist before promoting it to production.

Step 5: Re-enable Service Broker and Monitor

• Turn Service Broker back on with ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;.
• Re-run your application’s messaging workflows in a staging environment for 24-48 hours while monitoring the SQL Server Error Log for recurrence of Error 9679.

Alternative or Verification Steps

• Verify the fix by re-running the diagnostic query from Step 1 (sys.services vs sys.service_queues join check); it should return zero rows if the orphaned reference has been resolved.
• If errors persist across multiple objects, consider exporting all Service Broker conversation data, dropping and fully recreating the Service Broker configuration (contracts, message types, services, and queues) from a clean DDL script rather than patching individual objects.

Troubleshooting Guide: SQL Server Error 9677 – Service Contract Referencing Missing Message Type

Why This Error Happens

This error occurs when the Service Broker metadata becomes inconsistent, specifically when a service contract retains a reference to a message type ID that no longer exists in the sys.service_message_types catalog view. This typically results from an incomplete or interrupted DDL operation (such as a dropped message type that wasn’t properly cascaded), corruption in the system catalog tables, or a flawed database restore/migration process that failed to carry over dependent objects correctly. In rare cases, manual manipulation of system tables or a failed upgrade from an older SQL Server version to SQL Server 2025 can leave orphaned references behind.

Step-by-Step Resolution Guide

Step 1: Run DBCC CHECKDB to Confirm Catalog Corruption

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on your Windows Server 2025 / Windows 11 machine.
• Execute the following command against the database reporting the error:
• DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for any catalog consistency errors beyond the Service Broker contract issue, as this confirms whether the corruption is isolated or systemic.

Step 2: Query Service Broker Catalog Views to Identify the Orphaned Reference

• Run the following query to cross-reference the contract ID and message type ID from the error message:
• SELECT * FROM sys.service_contract_message_usages WHERE message_type_id = <the_missing_ID_from_error>;
• SELECT * FROM sys.service_contracts WHERE service_contract_id = <the_contract_ID_from_error>;
• Note the service_contract_id and message_type_id pairing, as you will need this to safely remove or rebuild the broken link.

Step 3: Drop and Recreate the Affected Service Contract

• Since direct editing of system catalog views is unsupported, script out the existing contract definition (if retrievable) using SSMS or sp_helptext.
• Drop the broken contract using:
• DROP CONTRACT [YourContractName];
• Recreate the contract with a valid, existing message type reference:
• CREATE CONTRACT [YourContractName] ([YourValidMessageType] SENT BY INITIATOR);
• If the contract cannot be dropped due to dependent services, first disable or drop the dependent Service Broker services temporarily.

Step 4: Restore from a Known-Good Backup if Corruption Persists

• Navigate to your backup directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\ on Windows Server 2025.
• Restore the database to a point in time before the corruption occurred using:
• RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Path\To\Backup.bak' WITH REPLACE;
• Re-apply any transaction logs as needed to minimize data loss, then re-verify Service Broker objects post-restore.

Alternative or Verification Steps

• After applying the fix, re-run DBCC CHECKDB and query sys.service_contract_message_usages again to confirm no orphaned message type IDs remain.
• If the issue persists or recurs frequently, consider rebuilding Service Broker entirely using ALTER DATABASE [YourDatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE; as a last resort, noting this will reset the Service Broker GUID and clear all existing conversations.

Why This Error Happens

Error 9675 is typically an informational message logged by the Service Broker engine during database upgrade, restore, or validation operations, indicating the number of message types it has scanned. While not strictly an “error” in the traditional sense, it frequently appears alongside Service Broker corruption issues when the sys.servicemessagetypes system catalog is out of sync with the sysobjvalues internal structure. This mismatch is most often caused by an interrupted upgrade process, a corrupted system database, or manual tampering with Service Broker objects.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance via a Dedicated Admin Connection (DAC) if the database is inaccessible.
• Run the following query to check if Broker is enabled: SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDB';
• If disabled unexpectedly, do not re-enable it yet—corruption may reappear immediately.

Step 2: Run DBCC CHECKDB to Isolate Catalog Corruption

• Execute DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for underlying consistency errors tied to message type objects.
• Pay close attention to errors referencing object_id values linked to sys.service_message_types or sys.service_contract_message_usages.
• If corruption is confirmed, proceed to restore from a known good backup rather than attempting a repair, as REPAIR_ALLOW_DATA_LOSS can permanently break Broker routing.

Step 3: Rebuild the Service Broker GUID (If No Corruption Found)

• If CHECKDB returns clean but the 9675 count mismatches expected message types, the Broker GUID may be orphaned.
• Run: ALTER DATABASE YourDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (Note: this clears all conversation queues, so document active message types first).
• Alternatively, use ALTER DATABASE YourDB SET ENABLE_BROKER; if the GUID itself is intact but the flag was dropped during a failed Windows Server 2025 patch cycle.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to the Microsoft Update Catalog and confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, as early builds have known Service Broker metadata logging bugs.
• Apply the update via Setup.exe /Action=Patch /IAcceptSQLServerLicenseTerms /Quiet from an elevated PowerShell prompt on Windows Server 2025.

Alternative or Verification Steps

• After applying fixes, re-run SELECT COUNT(*) FROM sys.service_message_types; and compare the output against the %d value reported in the original Error 9675 log entry to confirm alignment.
• If the count still mismatches, export Broker configuration using sp_help_service_broker and manually re-create missing message types using CREATE MESSAGE TYPE scripts from your source control backup.

Why This Error Happens

Error 9676 is generated by the SQL Server Service Broker engine during the internal validation of Conversation Priority objects and their associated Service Contracts. This message typically surfaces when the Broker’s priority resolution algorithm encounters a mismatch between the number of contracts registered in sys.service_contracts and the number it can successfully bind to active services. In most cases, this stems from an interrupted Service Broker configuration script, a corrupted system catalog view cache, or insufficient permissions on the msdb database that prevent the Broker from fully enumerating contract metadata during startup.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and GUID Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to confirm Broker is enabled and check the GUID:
  SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• If is_broker_enabled returns 0, enable it using:
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Rebuild the Service Contract Cache

• Navigate to the installation directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\.
• Stop the SQL Server service via Services.msc or PowerShell: Stop-Service MSSQLSERVER.
• Clear the plan cache for Service Broker by restarting the service, then execute:
  DBCC FREESYSTEMCACHE ('ALL');
• Re-run the contract analysis manually using:
  EXEC sys.sp_control_dbmasterkey_password; (if master key dependencies are suspected) or re-create the specific contract via CREATE CONTRACT.

Step 3: Check Permissions on System Objects

• Ensure the service account running SQL Server (visible in services.msc under “Log On As”) has db_owner or equivalent rights on msdb.
• Grant explicit permissions if needed:
  GRANT CONTROL ON CONTRACT::[YourContractName] TO [YourServiceAccount];
• Restart the SQL Server service to apply the permission changes.

Step 4: Validate with Windows Event Viewer

• Open Event Viewer on Windows Server 2025 and navigate to Applications and Services Logs > Microsoft > Windows > SQL Server.
• Filter for Event ID 9676 to confirm the timestamp aligns with your repair actions and check for correlating Broker errors (such as 9675 or 9677).

Alternative or Verification Steps

• Run SELECT * FROM sys.service_contracts; and compare the row count against the %d value reported in the original error log entry to confirm the discrepancy is resolved.
• If the error persists after cache rebuilding, drop and recreate the specific Service Broker Queue, Service, and Contract in a maintenance window, as this forces a full re-registration in the system catalog.

Troubleshooting Guide: SQL Server Error 9674 – “Conversation Groups Analyzed: %d”

Why This Error Happens

Error 9674 is an informational/diagnostic message generated by Service Broker during internal consistency checks or cleanup operations on conversation group metadata. It typically surfaces when the sys.conversation_endpoints or related Service Broker system tables contain orphaned or inconsistent conversation group references, often due to abrupt service termination, database corruption, or interrupted Service Broker cleanup tasks. This can also appear when DBCC or internal garbage collection routines are actively scanning a large volume of stale conversations that were never properly ended.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Configuration

• Confirm Service Broker is enabled on the affected database by running: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDBName';
• If disabled unexpectedly, re-enable it using: ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Check for blocking sessions holding locks on Service Broker system tables via sys.dm_tran_locks.

Step 2: Run Consistency Checks on the Database

• Execute DBCC CHECKDB('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify underlying corruption in system tables.
• Pay special attention to errors referencing sys.conversation_endpoints, sys.conversation_groups, or sys.transmission_queue.
• If corruption is found, review the recommended repair level before proceeding (avoid REPAIR_ALLOW_DATA_LOSS unless necessary).

Step 3: Clean Up Orphaned Conversations

• Identify stale or orphaned conversations using: SELECT * FROM sys.conversation_endpoints WHERE state IN ('ER','DI','CD');
• End orphaned conversations manually with: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER on Windows Server 2025 to force Service Broker to re-initialize its internal queues.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to Microsoft Update Catalog and confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, as known Service Broker diagnostic bugs are frequently patched.
• Install the CU using elevated privileges and reboot Windows Server 2025 if prompted.

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for recurrence of Error 9674 over a 24-hour period.
• If the message persists without negative side effects, treat it as informational; consider suppressing non-critical trace flags associated with Service Broker diagnostics (e.g., disabling verbose logging via DBCC TRACEOFF) after confirming no data consistency issues exist.

Troubleshooting Guide: SQL Server Error 9672

Why This Error Happens

Error 9672 is an informational message raised by Service Broker when it internally cleans up messages belonging to a conversation that has ended or timed out, typically due to conversation lifetime expiration or an explicit END CONVERSATION call. This is often triggered by orphaned conversation groups left behind when activation stored procedures fail silently, crash, or do not properly call RECEIVE/END CONVERSATION. In rare cases, it can also surface when the underlying Service Broker queue or its associated transmission queue has become corrupted after an unexpected server shutdown or improper failover in an Always On Availability Group setup.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Queue and Conversation

• Run the following query to locate the queue referenced by the ID in the error message:

SELECT name, object_id FROM sys.service_queues WHERE object_id = <queue_id>;

• Cross-reference the conversation handle from the error against sys.conversation_endpoints to determine its current state:

SELECT conversation_handle, state_desc, lifetime FROM sys.conversation_endpoints WHERE conversation_handle = '<handle>';

Step 2: Review and Fix the Activation Stored Procedure

• Open the activation procedure bound to the queue (check via sys.service_queues.activation_procedure).
• Ensure every code path includes a TRY...CATCH block and explicitly calls END CONVERSATION @handle after processing or on error.
• Verify there are no long-running transactions holding locks on the queue, which can cause messages to be force-removed when the conversation times out.

Step 3: Check for Orphaned Conversation Groups

• Run sys.transmission_queue to see if there are stuck or undeliverable messages contributing to broader queue instability.
• If orphaned groups exist, clean them up safely:

END CONVERSATION '<conversation_handle>' WITH CLEANUP;

• On Windows Server 2025, confirm the SQL Server Service Broker endpoint isn’t blocked by Windows Defender Firewall (default TCP port 4022) if this is a cross-database or cross-instance dialog.

Step 4: Validate Queue and Database Health

• Run DBCC CHECKDB on the affected database to rule out corruption as a root cause, especially if the server recently had an unexpected shutdown:

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

• If corruption is found specifically in Service Broker system tables, restore from the last known good backup rather than attempting repair, as REPAIR_ALLOW_DATA_LOSS can affect broker metadata integrity.

Alternative or Verification Steps

• Monitor SQL Server Error Log and Extended Events (broker_queue_disabled, broker_activation) over a 24-hour period to confirm Error 9672 no longer recurs after the activation procedure fix.
• If the issue persists, as a temporary workaround, disable and re-enable activation on the queue to force a clean state: ALTER QUEUE QueueName WITH ACTIVATION (STATUS = OFF); followed by STATUS = ON.

Troubleshooting Guide: SQL Server Error 9673 – Activation Disabled on Queue

Why This Error Happens

This error occurs when SQL Server’s Service Broker automatically disables activation on a queue after the activation stored procedure repeatedly fails or throws unhandled exceptions. This is a built-in safety mechanism designed to prevent an infinite loop of procedure failures from consuming excessive system resources. Common root causes include a corrupted or missing activation stored procedure, insufficient permissions for the service account to execute the procedure, or unhandled errors within the activation procedure itself causing repeated transaction rollbacks.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Queue and Review Error Logs

• Run the following query to find the queue referenced by the ID in the error: SELECT name, object_id, is_activation_enabled FROM sys.service_queues WHERE object_id = <queue_id>;
• Check the SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for additional stack trace details pointing to the exact failure in the activation stored procedure.
• Review sys.transmission_queue to check for any poison messages stuck in the queue causing repeated rollbacks.

Step 2: Fix the Underlying Activation Procedure Issue

• Open the stored procedure associated with the queue (check sys.service_queues for the activation_procedure column) and inspect it for unhandled TRY...CATCH exceptions.
• Verify the service account running SQL Server has EXECUTE permissions on the procedure and SEND/RECEIVE permissions on the queue using: GRANT RECEIVE ON [QueueName] TO [ServiceAccount];
• If a poison message is suspected, manually dequeue and inspect it using RECEIVE TOP(1) * FROM [QueueName]; inside a transaction you can roll back for analysis.

Step 3: Re-enable Activation on the Queue

• Once the procedure code or permissions issue is corrected, manually re-enable activation using: ALTER QUEUE [QueueName] WITH ACTIVATION (STATUS = ON);
• If the queue itself appears corrupted, consider dropping and recreating it along with its associated service and contract definitions.
• Restart the SQL Server Service Broker task via ALTER DATABASE [YourDB] SET ENABLE_BROKER; if the broker itself appears unresponsive.

Step 4: Validate Windows Server 2025 / Windows 11 Environment Settings

• Confirm Windows Firewall or Group Policy isn’t blocking the SQL Server process from spawning new activation threads (check Windows Defender Firewall with Advanced Security).
• Ensure the SQL Server service account has “Log on as a service” rights under Local Security Policy > User Rights Assignment.

Alternative or Verification Steps

• Verify the fix by checking is_activation_enabled returns 1 in sys.service_queues, and monitor sys.dm_broker_activated_tasks to confirm new activation tasks are spawning without immediate failure.
• If the issue persists, as an alternative, implement a TRY...CATCH block with explicit ROLLBACK and logging inside the activation stored procedure to prevent future automatic disabling, then test by sending a sample message to the queue via SEND ON CONVERSATION.

Troubleshooting Guide: SQL Server Error 9671

Why This Error Happens

Error 9671 occurs when Service Broker purges messages associated with a specific conversation ID from the transmission queue, typically because the conversation has exceeded its lifetime or encountered an unrecoverable transport failure. This is often triggered by a corrupted or orphaned conversation endpoint, a dropped queue or service definition while messages were still in transit, or network instability between SQL Server instances preventing successful delivery. In some cases, this also surfaces when the sys.transmission_queue contains stale entries referencing conversations that were forcibly ended via END CONVERSATION WITH CLEANUP.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Conversation and Queue

• Run the following query to inspect the transmission queue for problematic entries:

  SELECT * FROM sys.transmission_queue;

• Note the conversation_handle, from_service_name, and to_service_name values tied to the conversation ID in the error message.
• Cross-reference with sys.conversation_endpoints to check the state column (look for ER = Error or CD = Closing/Disconnected states).

Step 2: Validate Service Broker Configuration

• Confirm Service Broker is enabled on the database:

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

• If disabled, enable it with:

  ALTER DATABASE YourDBName SET ENABLE_BROKER;

• Check that the Queue and Service objects referenced by the conversation still exist and have not been dropped mid-transaction.
• Verify firewall rules on Windows Server 2025 / Windows 11 allow traffic on the Service Broker endpoint port (default 4022) via wf.msc or:

  Get-NetFirewallRule -DisplayName "*SQL Server Service Broker*"

Step 3: Clean Up Orphaned Conversations

• Forcefully end any stuck or erroring conversations identified in Step 1:

  END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• Restart the Service Broker task on the database if conversations remain stuck:

  ALTER DATABASE YourDBName SET DISABLE_BROKER;
  ALTER DATABASE YourDBName SET ENABLE_BROKER;

• Check SQL Server error logs for correlated network or certificate errors (common in encrypted Service Broker setups) using SSMS or %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 4: Re-sync Routing and Certificates (If Cross-Instance)

• If the conversation spans multiple SQL Server instances, validate routing entries:

  SELECT * FROM sys.routes;

• Re-issue or renew any expired Service Broker certificates used for dialog security:

  SELECT name, expiry_date FROM sys.certificates WHERE name LIKE '%Broker%';

• Ensure system clocks are synchronized between Windows Server 2025 instances (certificate validation is time-sensitive).

Alternative or Verification Steps

• After applying fixes, monitor sys.transmission_queue for a few minutes — a healthy system should show the queue draining to zero or near-zero entries without new 9671 errors appearing in the SQL Server error log.
• If the error persists, enable Service Broker diagnostic tracing via Extended Events (sqlserver.broker_conversation and sqlserver.broker_message_classify) to capture the exact failure point before messages are purged.

Troubleshooting Guide: SQL Server Error 9669 – “Conversation Endpoints Analyzed: %d”

Why This Error Happens

This informational/diagnostic message is generated by the Service Broker engine during its internal cleanup task (sysdesend/sysdercv cleanup) when it scans and analyzes conversation endpoints for orphaned or expired dialogs. It typically surfaces alongside broader Service Broker health issues, such as a buildup of unclosed conversations, corrupted dialog metadata, or a backlog in the sys.conversation_endpoints system view that the engine cannot resolve automatically. In most cases, the root cause is an application that opens Service Broker dialogs but fails to properly call END CONVERSATION, causing the endpoint count to grow until SQL Server logs this diagnostic entry.

Step-by-Step Resolution Guide

Step 1: Identify the Scope of Orphaned Conversations

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to check endpoint volume and state:

SELECT state_desc, COUNT(*) AS EndpointCount
FROM sys.conversation_endpoints
GROUP BY state_desc;

• Look specifically for endpoints in the CLOSED or DISCONNECTED_INBOUND/DISCONNECTED_OUTBOUND states, as these are the primary contributors to the analyzed count in Error 9669.

Step 2: Clean Up Orphaned or Expired Dialogs

• For databases with Service Broker enabled, identify and end stale conversations manually using:

DECLARE @handle UNIQUEIDENTIFIER;
DECLARE convo_cursor CURSOR FOR
SELECT conversation_handle FROM sys.conversation_endpoints
WHERE state = 'CD'; -- Closed state
OPEN convo_cursor;
FETCH NEXT FROM convo_cursor INTO @handle;
WHILE @@FETCH_STATUS = 0
BEGIN
    END CONVERSATION @handle WITH CLEANUP;
    FETCH NEXT FROM convo_cursor INTO @handle;
END
CLOSE convo_cursor;
DEALLOCATE convo_cursor;

• Ensure this is run during a maintenance window, as WITH CLEANUP forcibly removes conversation state without notifying the remote endpoint.

Step 3: Verify Service Broker Configuration Integrity

• Confirm Service Broker is enabled and not corrupted at the database level:

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

• If disabled unexpectedly or the GUID is mismatched, repair it with:

ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• On Windows Server 2025/Windows 11, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating Broker errors (e.g., Error 9675 or 9676) that often accompany 9669.

Step 4: Adjust the Diagnostic Interval (Optional Suppression)

• If the message is purely informational and recurring due to normal high-volume messaging workloads, you can reduce log noise by adjusting the trace flag behavior.
• Use Trace Flag 1222 or Extended Events to monitor Broker activity instead of relying on the default ERRORLOG entry, preventing alert fatigue from this specific event.

Alternative or Verification Steps

• Verify the fix: Re-run the sys.conversation_endpoints query from Step 1; the total count should stabilize or decrease after cleanup, and Error 9669 entries in the SQL Server log should no longer show an increasing %d value.
• Alternative solution: If orphaned endpoints continue to accumulate rapidly, audit the application code for missing END CONVERSATION calls or implement a scheduled SQL Agent job to run the cleanup script in Step 2 nightly as a permanent maintenance solution.

Troubleshooting Guide: SQL Server Error 9670 — Remote Service Bindings Analysis

Why This Error Happens

Error 9670 is a diagnostic/informational message generated during Service Broker consistency checks (typically via DBCC CHECKDB or internal Service Broker validation routines) that reports the count of Remote Service Bindings analyzed in the database. This message itself is not inherently an error, but it often surfaces alongside underlying issues such as corrupted Service Broker metadata, orphaned remote service binding entries, or certificate/security mapping mismatches between the local database and the remote service endpoints. In most cases, the root cause is a mismatch between the sys.remote_service_bindings catalog view entries and the actual certificates or users mapped to them, which can occur after a database restore, migration, or certificate rotation.

Step-by-Step Resolution Guide

Step 1: Confirm the Context of the Message

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS in SQL Server Management Studio (SSMS) to see if Error 9670 appears alongside actual corruption errors or purely as an informational count.
• Check the SQL Server Error Log (Management > SQL Server Logs in SSMS) for accompanying error codes (e.g., 9671–9675) that indicate specific binding corruption.
• Note the database name and timestamp to correlate with recent restores, migrations, or certificate changes.

Step 2: Validate Remote Service Binding Metadata

• Query the catalog view to inspect current bindings:

  SELECT * FROM sys.remote_service_bindings;
  SELECT * FROM sys.certificates;

• Verify that each principal_id in sys.remote_service_bindings maps to a valid, non-expired certificate in sys.certificates.
• If a binding references a dropped or expired certificate, drop the stale binding:

  DROP REMOTE SERVICE BINDING [BindingName];

Step 3: Recreate Valid Remote Service Bindings

• Re-establish the binding using a valid certificate and user mapping:

  CREATE REMOTE SERVICE BINDING [BindingName]
  TO SERVICE 'TargetServiceName'
  WITH USER = [ValidUserName];

• Ensure the associated certificate was imported correctly via CREATE CERTIFICATE ... FROM FILE = 'C:\Certs\remote_cert.cer'.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear cached Service Broker state.

Step 4: Run Repair Tools if Corruption Persists

• If DBCC CHECKDB reports actual corruption tied to Service Broker objects, run:

  DBCC CHECKDB (YourDatabaseName, REPAIR_REBUILD);

• Only use REPAIR_ALLOW_DATA_LOSS as a last resort, and always take a full backup first using BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backups\PreRepair.bak'.

Alternative or Verification Steps

• Re-run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS and confirm Error 9670 now reports a stable count with no accompanying corruption errors (9671+).
• If the issue persists, drop and fully recreate the Service Broker configuration using ALTER DATABASE YourDatabaseName SET ENABLE_BROKER followed by re-establishing all remote service bindings from a documented baseline script.

Troubleshooting Guide: SQL Server Error 9668 — Service Broker Queue Analysis

Why This Error Happens

Error 9668 is reported by the Service Broker queue monitor during its internal diagnostic pass, indicating the number of Service Queues (%d) that were analyzed for activation and message processing eligibility. This message typically surfaces when the queue monitor detects a mismatch between queue metadata and the actual activation stored procedures, often due to corrupted internal queue state, orphaned activation bindings, or a backlog of poison messages that forces the monitor to recheck all queues in the database. It can also occur after a database restore or failover where Service Broker GUIDs become inconsistent with the queue catalog, causing the monitor to re-analyze queues more frequently than expected.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on Windows Server 2025 / Windows 11.
• Run the following query to confirm Service Broker is enabled:

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

• If is_broker_enabled returns 0, enable it:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Identify and Clear Poison Messages

• Query the affected queues to check for disabled status caused by poison message handling:

SELECT name, is_receive_enabled, is_enqueue_enabled FROM sys.service_queues;

• Re-enable any queue that has been automatically disabled:

ALTER QUEUE [YourQueueName] WITH STATUS = ON;

• Inspect sys.transmission_queue for stuck or malformed messages causing repeated re-analysis.

Step 3: Rebuild Activation Procedure Bindings

• Navigate to the Queue properties in SSMS Object Explorer under Databases > YourDatabase > Service Broker > Queues.
• Drop and recreate the activation binding if the stored procedure reference is broken:

ALTER QUEUE [YourQueueName] WITH ACTIVATION (STATUS = OFF);
ALTER QUEUE [YourQueueName] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.YourActivationProc, MAX_QUEUE_READERS = 5, EXECUTE AS OWNER);

• Restart the SQL Server service via Services.msc on Windows Server 2025 to force the queue monitor to reinitialize cleanly.

Step 4: Check SQL Server Error Log and Event Viewer

• Open Event Viewer > Windows Logs > Application and filter for Event ID 9668 to correlate timestamps with other Broker-related errors (9675, 9676).
• Cross-reference with C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context on which queues triggered the analysis.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_broker_queue_monitors; to confirm the monitor state is now NOTIFIED_OF_ACTIVATION or idle without repeated analysis cycles.
• If the issue persists, consider running DBCC CHECKDB on the affected database to rule out underlying corruption impacting the Service Broker system tables.

Troubleshooting Guide: Error 9666 – Endpoint Disabled or Stopped State

Why This Error Happens

Error 9666 occurs when a SQL Server endpoint (such as the Database Mirroring, Service Broker, or TSQL endpoint referenced by %S_MSG) has its state explicitly set to DISABLED or STOPPED, preventing it from listening for incoming connections. This typically happens after a server restart where the endpoint state was not persisted correctly, a manual configuration change by an administrator, or a failed upgrade/migration process that left the endpoint in an inconsistent state. In some cases, security policy changes or certificate expiration tied to the endpoint can also trigger an automatic shutdown of the listener.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Endpoint and Its Current State

• Open SQL Server Management Studio (SSMS) and connect to the instance using an account with sysadmin privileges.
• Run the following query to list all endpoints and their states:

  SELECT name, protocol_desc, type_desc, state_desc 
  FROM sys.endpoints;

• Note the specific name of the endpoint showing DISABLED or STOPPED in the state_desc column.

Step 2: Restart the Endpoint Using T-SQL

• Execute an ALTER ENDPOINT command to force the endpoint back to a STARTED state.
• Use the exact name retrieved in Step 1 (e.g., Mirroring_Endpoint or TSQL_Default_TCP):

  ALTER ENDPOINT [Mirroring_Endpoint] STATE = STARTED;

• If the command fails due to a port conflict, check for other processes binding to the same port using netstat -ano | findstr <PORT> in PowerShell.

Step 3: Verify Certificate and Permission Validity

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ (path may vary based on your 2025 instance ID) to check for encryption certificate files if the endpoint uses SSL/TLS.
• Ensure the SQL Server Service Account has “Read” permissions on the certificate’s private key via the Windows Certificate Manager (certlm.msc).
• If the certificate has expired, you must drop the endpoint and recreate it with a renewed certificate, as expired certificates will cause the STATE = STARTED command to fail silently.

Step 4: Check Windows Firewall and Server Configuration

• On Windows Server 2025, open Windows Defender Firewall with Advanced Security.
• Verify that there is an active Inbound Rule allowing traffic on the specific TCP port used by the endpoint (commonly 5022 for mirroring).
• Restart the SQL Server service via services.msc to ensure the configuration change takes effect cleanly: net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Verify the Fix: Re-run the SELECT name, state_desc FROM sys.endpoints; query. The state_desc column should now display STARTED for the specific endpoint.
• Alternative Solution: If the endpoint remains stuck in a STOPPED state after a restart, drop the endpoint entirely using DROP ENDPOINT [EndpointName] and recreate it from scratch using the CREATE ENDPOINT syntax specific to its type (Database Mirroring, Service Broker, or SOAP).

Why This Error Happens

Error 9667 is generated by the SQL Server Service Discovery component, which runs periodically to enumerate and validate Windows services related to SQL Server (Database Engine, Agent, Browser, and Always On health services) for the Resource Governor and Cluster-Aware Updating integration. This error typically surfaces when the Service Control Manager (SCM) query made by SQL Server’s background worker thread is blocked due to insufficient WMI permissions or a corrupted service registry hive, causing the scan to report an incomplete or unexpected count (%d) of analyzed services. It can also occur after an in-place upgrade to SQL Server 2025 where the WMI provider (MOF file) was not re-registered correctly against the new binary path.

Step-by-Step Resolution Guide

Step 1: Verify WMI Provider Registration

• Open an elevated Command Prompt on the Windows Server 2025 / Windows 11 host.
• Navigate to the SQL Server 2025 binaries folder (typically C:\Program Files\Microsoft SQL Server\170\Shared).
• Run mofcomp sqlmgmproviderxpsp2up.mof to re-register the WMI provider used for service discovery.
• Restart the Winmgmt service: net stop winmgmt followed by net start winmgmt.

Step 2: Validate Service Account Permissions

• Open Services.msc and confirm the SQL Server service account has “Log on as a service” rights (via secpol.msc > Local Policies > User Rights Assignment).
• Ensure the account is a member of the local Distributed COM Users and Performance Monitor Users groups, both required for SCM/WMI queries.
• If using a Managed Service Account (MSA) or gMSA, re-run Set-ADServiceAccount to refresh Kerberos delegation settings.

Step 3: Repair the Service Registry Hive

• Open regedit and navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services.
• Check for orphaned or duplicate SQL-related service keys (e.g., leftover entries from a failed uninstall of a previous SQL Server version).
• Export the hive as backup, then remove any corrupted keys referencing non-existent ImagePath binaries.
• Reboot the server to allow SCM to rebuild its internal service cache.

Step 4: Reapply SQL Server 2025 Setup Repair

• Run Setup.exe /ACTION=REPAIR /INSTANCENAME=MSSQLSERVER /QUIET from the installation media.
• This re-registers the diagnostic DLLs (sqlsvc.dll, sqlos.dll) responsible for generating the Service Discovery report.
• Confirm Error 9667 no longer appears in the SQL Server Error Log after the repair completes.

Alternative or Verification Steps

• After applying the fix, run EXEC sp_server_diagnostics or check the SQL Server Error Log (ERRORLOG file) to confirm the “Services analyzed” count matches the expected number of installed SQL services with no accompanying error text.
• If the issue persists, disable and re-enable the SQL Server Browser service, as a stalled Browser service frequently causes incomplete WMI-based service enumeration.

Troubleshooting Guide: Error 9663 – “System Object Cannot Be Modified” (SQL Server 2025)

Why This Error Happens

Error 9663 occurs when an attempt is made to alter, drop, or otherwise modify a SQL Server system object, which is protected by design to maintain database engine integrity. This typically happens when a maintenance script, third-party tool, or administrator inadvertently targets a system table, system view, or system stored procedure instead of a user-defined object. It can also surface if a deployment script was written for an older SQL Server version and references internal metadata objects that have changed structure or protection status in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Object Triggering the Error

• Check the full error message in SQL Server Management Studio (SSMS) or the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Note the object name and schema (often prefixed with sys. or found in the sys.objects catalog view with is_ms_shipped = 1).
• Run the following query to confirm if the object is system-protected:

  SELECT name, type_desc, is_ms_shipped 
  FROM sys.objects 
  WHERE name = 'YourObjectName';

Step 2: Review and Correct the Offending Script or Application Logic

• Open the deployment, migration, or maintenance script that issued the ALTER, DROP, or UPDATE command.
• Replace any direct reference to the system object with the correct user-defined object or supported system stored procedure (e.g., use sp_rename cautions or official DMVs instead of direct table edits).
• If using a third-party backup, monitoring, or ORM tool, check for a pending update, as older drivers sometimes assume deprecated schema behavior from SQL Server 2019/2022.

Step 3: Verify Permissions and Dedicated Admin Connection (DAC) Usage

• Confirm that no process is attempting to bypass security via the Dedicated Admin Connection (DAC) to force changes on system tables.
• In SSMS, ensure “Allow updates to system tables” (a legacy sp_configure option) is not enabled, as this is deprecated and blocked in SQL Server 2025:

  EXEC sp_configure 'allow updates', 0;
  RECONFIGURE;

• Restart the SQL Server service via Windows Services (services.msc) or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Apply the Change via Supported Mechanisms

• For legitimate configuration changes, use supported system stored procedures (e.g., sp_configure, sp_rename for user objects, or ALTER DATABASE scoped configurations) rather than direct DML/DDL against system objects.
• If the requirement is to extend system functionality, create a new user-defined object in a custom schema instead of modifying the shipped system object.

Alternative or Verification Steps

• Re-run the original script or operation and confirm Error 9663 no longer appears in the SSMS output or ERRORLOG.
• If the issue persists, restore a recent database backup to a test environment and compare sys.objects metadata to rule out corruption, then contact Microsoft Support with the Error Log and script details for further diagnosis.

Why This Error Happens

This error occurs when a Service Broker ROUTE object is created or altered without explicitly defining both the SERVICE_NAME and BROKER_INSTANCE arguments while the database is configured for database mirroring or is part of an Always On Availability Group. SQL Server 2025 requires these two parameters to be fully specified in mirrored environments because the broker needs a deterministic way to redirect message traffic to the correct partner instance during a failover. Omitting either value leaves the routing table ambiguous, causing the engine to reject the route definition at creation time.

Step-by-Step Resolution Guide

Step 1: Identify the Faulty Route Definition

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on Windows Server 2025 or Windows 11.
• Run SELECT * FROM sys.routes; to list existing routes and locate the one referenced in the error (the %.*ls placeholder in the message).
• Note the address, mirror_address, and broker_instance columns, as these will indicate what is missing.

Step 2: Drop and Recreate the Route with Full Parameters

• Execute DROP ROUTE [RouteName]; to remove the incomplete route.
• Recreate it using a complete CREATE ROUTE statement that explicitly includes both required values:

  CREATE ROUTE [RouteName]
      WITH SERVICE_NAME = 'TargetServiceName',
      BROKER_INSTANCE = '12345678-ABCD-1234-ABCD-1234567890AB',
      ADDRESS = 'TCP://PrimaryServer:4022',
      MIRROR_ADDRESS = 'TCP://MirrorServer:4022';

• Retrieve the correct BROKER_INSTANCE GUID by running SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName'; on the target database.

Step 3: Validate Mirroring/AG Configuration Paths

• Confirm the mirroring endpoint is active via SELECT * FROM sys.database_mirroring_endpoints; to ensure the TCP ports (default 4022) are correctly bound.
• On Windows Server 2025, check Windows Firewall rules under Control Panel > Windows Defender Firewall > Advanced Settings to confirm inbound/outbound rules allow traffic on the Service Broker and mirroring ports.
• If using an Availability Group, verify the listener and replica addresses match the ADDRESS and MIRROR_ADDRESS values used in the route.

Step 4: Restart the Service Broker and Apply Changes

• Run ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER; if the broker was disabled during troubleshooting.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to ensure the new route metadata is loaded cleanly.

Alternative or Verification Steps

• Verify the fix by querying sys.routes again and confirming the broker_instance and address columns are fully populated with no NULL values for the affected route.
• If the error persists, use SELECT * FROM sys.transmission_queue; to check for stuck messages, and consider manually re-initiating mirroring failover testing in a non-production environment to confirm message routing works end-to-end.

Troubleshooting Guide: SQL Server Error 9662 – “Cannot specify BROKERINSTANCE without SERVICENAME in route”

Why This Error Happens

This error occurs when creating or altering a Service Broker ROUTE object where the BROKER_INSTANCE argument is specified but the mandatory SERVICE_NAME argument is missing from the CREATE ROUTE or ALTER ROUTE statement. SQL Server 2025 enforces strict dependency validation on route parameters because BROKER_INSTANCE is only meaningful when paired with a target service, since it identifies a specific broker instance for that named service. This is typically caused by a manually written or auto-generated T-SQL script that was copied incorrectly, edited incompletely, or generated by an outdated migration/deployment tool that doesn’t account for Service Broker’s parameter dependency rules.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Route Script

• Identify the exact script or stored procedure that executes the CREATE ROUTE or ALTER ROUTE statement throwing Error 9662.
• Check deployment logs, SSDT project files (.sql route definition files), or DACPAC deployment scripts under your project’s Security\Routes\ folder.
• Open SQL Server Management Studio (SSMS) and run the failing script in isolation to confirm the exact line number and route name referenced in the error message ("%.*ls").

Step 2: Correct the ROUTE Definition Syntax

• Edit the script to include a valid SERVICE_NAME parameter alongside BROKER_INSTANCE.
• Correct syntax example:

CREATE ROUTE MyRoute
    WITH SERVICE_NAME = 'TargetServiceName',
    BROKER_INSTANCE = '12345678-1234-1234-1234-123456789ABC',
    ADDRESS = 'TCP://remoteserver.domain.com:4022';

• If you do not have the target service name, query the remote instance’s sys.services and sys.service_broker_endpoints catalog views to retrieve the correct SERVICE_NAME and BROKER_INSTANCE GUID values.
• If BROKER_INSTANCE isn’t strictly required for your routing scenario (e.g., routing without instance-level disambiguation), remove it entirely and leave only SERVICE_NAME and ADDRESS.

Step 3: Drop and Recreate the Faulty Route

• If the route object was partially created or exists in a broken state from a prior failed transaction, drop it first:

IF EXISTS (SELECT * FROM sys.routes WHERE name = 'MyRoute')
    DROP ROUTE MyRoute;

• Re-run the corrected CREATE ROUTE script from Step 2 on both the initiator and target databases.
• Restart the SQL Server Service Broker task via ALTER DATABASE [YourDB] SET ENABLE_BROKER; if the broker was disabled during troubleshooting.

Step 4: Validate Deployment Tooling and Source Control

• If the error originated from an automated deployment (DACPAC, Azure DevOps pipeline, or Redgate/Flyway script), update the source .sql file in version control to prevent regression.
• Re-run a schema compare in SSDT or Visual Studio to confirm the corrected route definition matches the deployed target.

Alternative or Verification Steps

• Verify the fix by querying SELECT * FROM sys.routes WHERE name = 'MyRoute'; and confirming both remote_service_name and broker_instance columns are populated as expected (not NULL).
• As an alternative, if you don’t need instance-specific routing, simplify the route by omitting BROKER_INSTANCE entirely — SQL Server will route based on SERVICE_NAME alone, which resolves the error without needing the GUID lookup.

Troubleshooting Guide: SQL Server Error 9660 — Route Identifier Exceeds Character Limit

Why This Error Happens

Error 9660 occurs when a Service Broker route definition contains a parameter—such as the route name, address, or service name—that exceeds the maximum allowed character length defined by SQL Server’s internal schema constraints. This typically happens when scripts are migrated between environments using concatenated or dynamically generated identifiers that weren’t validated beforehand. It can also surface during automated deployment pipelines where route names are programmatically built using GUIDs, timestamps, or environment variables without length checks.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Route and Parameter

• Review the exact error message text to determine which parameter (name, address, or mirror_address) triggered the violation.
• Run the following query to inspect existing routes:

SELECT name, address, mirror_address, LEN(name) AS NameLen, LEN(address) AS AddressLen
FROM sys.routes;

• Cross-reference the %d character limit shown in the error output (commonly 255 for names, 4000 for addresses) against your actual string length.

Step 2: Correct the Route Creation Script

• Open the T-SQL script or deployment template responsible for generating the CREATE ROUTE or ALTER ROUTE statement.
• Locate the string concatenation logic (often found in PowerShell DSC configs, Azure DevOps YAML pipelines, or .sql deployment files).
• Truncate or shorten the identifier using LEFT() or substring logic, for example:

CREATE ROUTE [MyRoute]
    AUTHORIZATION [dbo]
    WITH SERVICE_NAME = 'MyServiceName',
    ADDRESS = 'TCP://servername:4022';

• Ensure the ADDRESS value does not include unnecessary verbose paths or redundant domain suffixes.

Step 3: Validate Naming Conventions in Automation Scripts

• Check any PowerShell or SSDT (SQL Server Data Tools) publish profiles on Windows Server 2025 that auto-generate route names using variables like $env:COMPUTERNAME or [guid]::NewGuid().
• Implement a length-validation function before execution:

if ($routeName.Length -ge 255) {
    $routeName = $routeName.Substring(0,250)
}

• Re-run the deployment script after adjusting the variable length caps.

Step 4: Rebuild the Route in SQL Server Management Studio (SSMS)

• Drop the invalid route using DROP ROUTE [RouteName];.
• Recreate it manually via SSMS Object Explorer under Service Broker > Routes, ensuring the Name and Address fields stay within the documented limits.
• Save and execute the script, confirming no red underlines or IntelliSense warnings appear for long string literals.

Alternative or Verification Steps

• Re-run the original Service Broker initialization script and confirm no Error 9660 appears in SQL Server Logs (via SSMS > Management > SQL Server Logs) or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the error persists, use sys.dm_broker_connections to confirm the route is active and correctly linked to the intended service, verifying that truncation did not break the service-to-route mapping.

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.

Troubleshooting Guide: SQL Server Error 9659 – “The %s of route ‘%.*ls’ cannot be empty”

Why This Error Happens

Error 9659 occurs when a Service Broker route is created or altered with a required parameter—such as ADDRESS, SERVICENAME, or BROKERINSTANCE—left as an empty string instead of a valid value or NULL. This typically happens due to malformed T-SQL scripts, automated deployment tools passing blank variables, or manual configuration errors when setting up Service Broker routing for message delivery. SQL Server enforces this validation strictly because an empty value would make the route unusable for directing conversation endpoints to the correct destination.

Step-by-Step Resolution Guide

Step 1: Identify the Faulty Route Definition

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to locate existing routes and inspect their properties:

  SELECT name, address, mirror_address, service_name, broker_instance 
  FROM sys.routes;

• Check the script or application log that triggered the CREATE ROUTE or ALTER ROUTE statement to find which parameter was passed as an empty string ('').

Step 2: Correct the T-SQL Script Parameters

• Edit the script and ensure every required field has a valid value. For example, the ADDRESS parameter must be either a valid network location or the literal keyword TRANSPORT, never ''.
• Example of a corrected statement:

  CREATE ROUTE MyRoute
  WITH SERVICE_NAME = 'MyService',
       ADDRESS = 'TCP://ServerName:4022';

• If using a deployment tool (e.g., Azure DevOps, SSDT, or PowerShell DSC) on Windows Server 2025, verify that variable substitution isn’t injecting blank strings due to missing environment variables.

Step 3: Drop and Recreate the Route Safely

• If a route already exists in a broken state, drop it first:

  DROP ROUTE IF EXISTS MyRoute;

• Recreate it using the corrected, non-empty parameters from Step 2.
• Restart the SQL Server Service Broker task if conversations were stuck mid-process:

  ALTER DATABASE MyDB SET NEW_BROKER;

Step 4: Validate Windows Server/Application Configuration Variables

• On Windows Server 2025 or Windows 11 machines running the SQL client or deployment pipeline, check %SystemRoot%\System32\drivers\etc\hosts and any .env or .config files used by your deployment scripts for blank address entries.
• Ensure firewall and network configuration (TCP port 4022, by default) is correctly set, as this is often where the “address” value originates from automated scripts.

Alternative or Verification Steps

• Verify the fix by querying sys.routes again to confirm no NULL/empty fields remain, and test sending a message through the route using SEND ON CONVERSATION to ensure successful delivery.
• If the error persists despite correct scripts, check for cached or replicated metadata issues by restarting the SQL Server service via services.msc on Windows Server 2025, or use sp_helpdb to confirm the Service Broker GUID is correctly enabled for the database.

Troubleshooting Guide: SQL Server Error 9657 – Service Broker Transmission Work-Table Corruption

Why This Error Happens

Error 9657 occurs when SQL Server detects that the internal Service Broker transmission work-table structure in tempdb has become malformed or corrupted, which typically points to underlying disk I/O failures, memory errors, or abrupt SQL Server shutdowns that interrupted writes to tempdb. Since tempdb is recreated at every service restart, this error often signals a transient hardware/storage issue rather than permanent database corruption, but it can also stem from a failing disk subsystem or faulty RAM on the Windows Server 2025 host. In rarer cases, antivirus or backup software locking tempdb data/log files mid-operation can trigger the same structural inconsistency.

Step-by-Step Resolution Guide

Step 1: Restart the SQL Server Service to Rebuild tempdb

• Open SQL Server Configuration Manager or use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force
• Since tempdb is rebuilt from scratch on every restart, this immediately clears any corrupted transmission work-table structures.
• Confirm the service started cleanly by checking SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for successful tempdb initialization messages.

Step 2: Run DBCC CHECKDB to Rule Out Deeper Corruption

• Execute DBCC CHECKDB('tempdb') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SQL Server Management Studio (SSMS) to validate structural integrity.
• Also run DBCC CHECKDB against your user databases that use Service Broker, since corruption can originate from message queues feeding into tempdb.
• Review output for allocation or consistency errors (Msg 8909, 8928, etc.) that may indicate broader corruption beyond tempdb.

Step 3: Validate Underlying Storage and Memory Hardware

• Open Windows Event Viewer (eventvwr.msc) and inspect the System log under Windows Logs for Disk, NTFS, or Storport warnings/errors (Event IDs 7, 11, 51, or 157).
• Run chkdsk C: /F /R (or the drive hosting tempdb files) during a scheduled maintenance window to repair file system-level issues.
• Use Windows Memory Diagnostic (mdsched.exe) or a manufacturer-specific memory testing tool to rule out faulty RAM modules on the Windows Server 2025 host.

Step 4: Relocate tempdb Files to Isolate Storage-Specific Faults

• Check current tempdb file paths using SELECT name, physical_name FROM sys.master_files WHERE database_id = 2;
• Move tempdb data and log files to a different, verified-healthy disk volume using ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLData\tempdb.mdf');
• Restart the SQL Server service afterward to apply the file relocation and confirm the new paths take effect.

Alternative or Verification Steps

• After restarting, generate Service Broker traffic (e.g., send a test message via SEND ON CONVERSATION) and monitor the SQL Server Error Log for recurrence of Error 9657.
• If the error persists after hardware validation, consider running SQLIOSim or Microsoft’s SQL Server Diagnostic (SQLDIAG) tool to stress-test I/O subsystems and isolate whether the fault lies in storage controllers, drivers, or physical disks.

Troubleshooting Guide: Service Broker Multicast Manager Error (Error 9656)

Why This Error Happens

Error 9656 occurs when the SQL Server Service Broker’s multicast manager is unable to properly distribute messages to multiple conversation endpoints, typically due to corrupted Service Broker internal queues or conversation groups. This can also stem from insufficient permissions on the msdb or user database’s Service Broker objects, or from a mismatch in the Service Broker GUID after a database restore/migration. Additionally, network connectivity issues between SQL Server endpoints involved in multicast conversations can trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and GUID Consistency

• Run SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is enabled.
• If the database was restored or copied, check for GUID conflicts using ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (use with caution as this resets conversations).
• Ensure no duplicate broker GUIDs exist across databases on the same instance.

Step 2: Inspect and Clear Corrupted Conversation Endpoints

• Query sys.conversation_endpoints and sys.transmission_queue for stalled or orphaned conversations.
• Use the command: SELECT * FROM sys.transmission_queue WHERE transmission_status != ''; to identify failed message transmissions.
• End problematic conversations manually using END CONVERSATION 'conversation_handle' WITH CLEANUP;.

Step 3: Validate Permissions on Service Broker Objects

• Confirm the service account running SQL Server 2025 has CONTROL permission on the affected database.
• Grant necessary permissions using: GRANT SEND ON SERVICE::[YourServiceName] TO [YourLogin];
• Verify the SQL Server service account has appropriate access under Windows Server 2025 Local Security Policy at Control Panel > Administrative Tools > Local Security Policy > Local Policies > User Rights Assignment.

Step 4: Restart Service Broker and SQL Server Service

• Disable and re-enable the broker: ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Restart the SQL Server service via Services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force
• Check SQL Server Error Logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurring 9656 entries post-restart.

Step 5: Check Firewall and Network Port Configuration

• Verify the Service Broker endpoint port (commonly TCP 4022) is open on Windows Defender Firewall with Advanced Security.
• Run netstat -ano | findstr 4022 in PowerShell to confirm the port is listening and not blocked.
• Adjust firewall rules via New-NetFirewallRule -DisplayName "SQL Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow.

Alternative or Verification Steps

• Verify the fix by monitoring sys.transmission_queue for an empty result set and checking that sys.dm_broker_connections shows active, healthy connections without repeated error states.
• If the error persists, consider running DBCC CHECKDB on the affected database to rule out underlying corruption, and review the SQL Server Profiler/Extended Events trace for the exact conversation handle triggering Error 9656.

Troubleshooting Guide: SQL Server Error 9654 – Database Does Not Exist

Why This Error Happens

Error 9654 typically occurs when the database referenced in a connection string or query has been detached, dropped, renamed, or never successfully attached to the SQL Server instance. This can also happen if the application is pointing to the wrong SQL Server instance (e.g., a named instance vs. default instance) or if the database exists but under a different name due to a case-sensitivity mismatch or restoration error. Additionally, corrupted system catalog metadata in the master database can cause SQL Server to report a valid database as “non-existent.”

Step-by-Step Resolution Guide

Step 1: Verify the Database Actually Exists on the Instance

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to list all databases currently attached:

  SELECT name, database_id, state_desc FROM sys.databases;

• Confirm the exact spelling, case, and underscores match what your application is requesting.
• If the database is missing entirely, check your backup directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\) for a recent .bak file to restore.

Step 2: Confirm the Application Is Targeting the Correct Instance

• Check the connection string in your application’s config file (e.g., web.config, appsettings.json) for the Server= or Data Source= parameter.
• Ensure it specifies the correct instance name, such as SERVERNAME\INSTANCENAME, rather than defaulting to localhost or (local).
• Open SQL Server Configuration Manager and verify the instance name under SQL Server Services matches what the application expects.

Step 3: Re-Attach or Restore the Missing Database

• If the .mdf and .ldf files still exist on disk (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\), attach them manually:

  CREATE DATABASE YourDatabaseName 
  ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDatabaseName.mdf')
  FOR ATTACH;

• If files are missing, restore from the latest backup using:

  RESTORE DATABASE YourDatabaseName 
  FROM DISK = 'C:\Backups\YourDatabaseName.bak' 
  WITH REPLACE;

Step 4: Check for Permissions or Orphaned User Mapping Issues

• Run EXEC sp_helpdb YourDatabaseName; to confirm the database is online and accessible.
• Verify the SQL login being used by the application has proper mapping under Security > Logins > User Mapping in SSMS.
• If needed, fix orphaned users with:

  ALTER USER YourUsername WITH LOGIN = YourLoginName;

Alternative or Verification Steps

• After applying the fix, restart the application and attempt the database connection again — confirm no Error 9654 appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• As a quick alternative check, run SELECT DB_ID('YourDatabaseName'); — if it returns NULL, the database is still not recognized and Steps 1–3 should be repeated with closer attention to name spelling and instance targeting.

Troubleshooting Guide: SQL Server Error 9655 – Transmission Queue Table Structure Inconsistency

Why This Error Happens

Error 9655 occurs when the internal system table structure of a Service Broker transmission queue becomes corrupted or inconsistent, typically caused by abrupt database shutdowns, disk-level I/O failures, or storage subsystem errors that interrupted a write operation mid-transaction. This can also surface after an improper failover in an Always On Availability Group where the transaction log was not fully synchronized before the queue metadata was committed. In rare cases, antivirus or backup software locking database files during active I/O on Windows Server 2025 can trigger partial writes that corrupt the internal queue schema.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Open SQL Server Management Studio (SSMS) and run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption is isolated to the transmission queue or affects other system tables.
• Check the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or storage warnings around the same timestamp as Error 9655.
• Note the exact queue name referenced in the error message (e.g., sys.transmission_queue) for targeted repair.

Step 2: Disable Service Broker Temporarily

• Run ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; to stop active message transmission and prevent further writes to the corrupted queue structure.
• Confirm Service Broker is disabled by querying SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; (result should return 0).
• This step prevents additional data loss while repair operations are performed.

Step 3: Run Targeted Repair with DBCC CHECKDB

• Put the database into single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — note this may clear orphaned or corrupted queue entries, so ensure a current backup exists first.
• Revert to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Restore from Backup if Repair Fails

• If DBCC CHECKDB cannot resolve the inconsistency, restore the database from the last known good full backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'E:\Backups\YourDatabaseName.bak' WITH REPLACE;
• Apply subsequent transaction log backups to minimize data loss up to the point of corruption.
• Re-enable Service Broker afterward with ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 5: Check Storage and Driver Health

• On Windows Server 2025, open Event Viewer (eventvwr.msc) and review the System log under Disk and NTFS sources for hardware-level warnings.
• Update storage controller drivers via Device Manager and run chkdsk E: /f /r (replace E: with your data drive) during a maintenance window to rule out underlying disk corruption.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; and confirm it returns zero allocation or consistency errors, then monitor the SQL Server Error Log for 24–48 hours to ensure Error 9655 does not recur.
• If corruption persists after repair and restore attempts, engage Microsoft Customer Support Services (CSS) with the DBCC CHECKDB output and a database backup for advanced forensic recovery.

Troubleshooting Guide: Error 9653 – Invalid Activation Stored Procedure Signature

Why This Error Happens

This error occurs when a stored procedure designated as a Service Broker activation procedure is defined with input parameters, which is not permitted by SQL Server’s architecture. Activation procedures are invoked internally by the Service Broker engine and cannot accept parameters because there is no mechanism for the engine to pass values to them at runtime. This typically happens after a procedure is altered by a developer who adds parameters without realizing it is bound to a queue as an activation stored procedure.

Step-by-Step Resolution Guide

Step 1: Identify the Queue and Procedure Binding

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server 2025 instance.
• Run the following query to identify which queue is using the faulty procedure:

SELECT name, activation_procedure 
FROM sys.service_queues 
WHERE activation_procedure LIKE '%YourProcedureName%';

• Note the exact queue name and schema for use in later steps.

Step 2: Modify the Stored Procedure Definition

• Open the stored procedure definition in SSMS (right-click the procedure > Modify).
• Remove any @parameter declarations from the CREATE PROCEDURE or ALTER PROCEDURE statement header.
• If the procedure requires dynamic data (e.g., conversation handles), ensure it uses RECEIVE statements internally to pull data from the queue rather than accepting it as an input parameter.
• Save and execute the ALTER PROCEDURE script to update the object in the database.

Step 3: Re-bind the Activation Procedure to the Queue

• Since the queue registration may have failed or detached, explicitly reset the activation setting using ALTER QUEUE.
• Run the following T-SQL command, replacing placeholders with your specific queue and procedure names:

ALTER QUEUE [YourSchema].[YourQueueName] 
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = [YourSchema].[YourProcedureName],
    MAX_QUEUE_READERS = 5,
    EXECUTE AS OWNER
);

• Verify there are no syntax errors in the output pane after execution.

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

• If the queue remains unresponsive after altering the procedure, restart the SQL Server service to clear the internal Broker cache.
• Open Services.msc on your Windows Server 2025 machine.
• Locate SQL Server (MSSQLSERVER) or your specific named instance, right-click, and select Restart.

Alternative or Verification Steps

• Verification: Send a test message to the service associated with the queue and monitor the sys.transmission_queue and sys.service_queues views to confirm the activation procedure fires without generating Error 9653 in the SQL Server Error Log.
• Alternative: If parameters are strictly required for your business logic, create a wrapper procedure with zero parameters that internally calls the original parameterized procedure using hardcoded or queue-retrieved values, then bind the wrapper (not the original) to the queue activation setting.

Troubleshooting Guide: Service Broker Error 9652

Why This Error Happens

Error 9652 occurs when SQL Server’s Service Broker cannot retrieve the session key needed to encrypt a dialog message, typically because the master key or database master key (DMK) is missing, not open, or inaccessible in the current session. This often happens when the database master key hasn’t been decrypted/opened, the Service Master Key is corrupted, or the certificate/key used for the conversation has expired or been dropped. Permission issues preventing SQL Server’s service account from accessing the key material in the database can also trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify and Open the Database Master Key

• Connect to the affected database using SQL Server Management Studio (SSMS) on your Windows Server 2025 / Windows 11 instance.
• Run the following query to check if the DMK exists:

  SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

• If missing, create it with:

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

• If it exists but is closed, explicitly open it in the session:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword123!';

Step 2: Validate the Service Master Key Integrity

• Open a new query window in SSMS and run:

  SELECT * FROM sys.key_encryptions WHERE crypt_type = 'SMKS';

• If corruption is suspected, back up the Service Master Key first:

  BACKUP SERVICE MASTER KEY TO FILE = 'C:\Backup\ServiceMasterKey.key' ENCRYPTION BY PASSWORD = 'BackupPass!23';

• Restore or regenerate it only if necessary, as this impacts all encrypted data in the instance.

Step 3: Check Certificate and Endpoint Configuration

• Verify that the certificate used by the Service Broker endpoint has not expired:

  SELECT name, expiry_date FROM sys.certificates;

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ to confirm certificate files referenced in the database are present and not corrupted.
• If expired or missing, create a new certificate and update the Service Broker endpoint binding accordingly.

Step 4: Restart the SQL Server Service and Re-enable Broker

• Open Services.msc on Windows Server 2025 / Windows 11.
• Restart the SQL Server (MSSQLSERVER) service to clear any cached session key errors.
• Re-enable Service Broker on the database if it was disabled:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Alternative or Verification Steps

• Confirm the fix by sending a test message through Service Broker and checking the sys.transmission_queue view for errors; an empty queue with no error messages indicates success.
• If the issue persists, drop and recreate the Service Broker GUID using ALTER DATABASE YourDatabaseName SET NEW_BROKER (note: this breaks existing conversations, so use only in non-production environments or after proper backups).

Troubleshooting Guide: Error 9650 – System Cryptographic Call Failed (SQL Server Service Broker / Database Mirroring)

Why This Error Happens

Error 9650 occurs when SQL Server’s Service Broker or Database Mirroring endpoint attempts to perform an encryption or certificate-based operation, but the underlying Windows cryptographic subsystem rejects the call. This is typically caused by the SQL Server service account lacking proper permissions on the certificate’s private key, a corrupted or expired certificate in the machine/user certificate store, or restrictive Windows CNG (Cryptography Next Generation) key storage provider settings. It can also surface after an OS upgrade to Windows Server 2025/Windows 11 where legacy CAPI certificates lose compatibility with updated cryptographic providers.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate Used by Service Broker/Mirroring

• Run the following query to confirm which certificate is bound to the endpoint:
• SELECT * FROM sys.certificates WHERE pvt_key_encryption_type_desc <> 'NO_PRIVATE_KEY';
• Check expiration dates and issuer validity — expired certificates are the most common trigger.
• Cross-reference with SELECT * FROM sys.database_mirroring_endpoints; or sys.service_broker_endpoints; to confirm endpoint-certificate mapping.

Step 2: Fix Private Key Permissions on the Certificate

• Open certlm.msc (Local Machine certificate store) on Windows Server 2025/Windows 11.
• Locate the certificate under Personal > Certificates, right-click → All Tasks > Manage Private Keys.
• Add the SQL Server service account (e.g., NT Service\MSSQLSERVER or custom domain account) with Read permission.
• If using PowerShell, run:
• Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object {$_.Subject -like "*YourCertName*"} | Format-List
• Confirm the private key is marked as exportable and not hardware-locked unless intended.

Step 3: Rebuild or Re-import the Certificate if Corrupted

• If the certificate shows as valid but the error persists, export it (with private key, .pfx format) and delete it from the store.
• Re-import using:
• Import-PfxCertificate -FilePath "C:\Certs\broker.pfx" -CertStoreLocation Cert:\LocalMachine\My -Password (ConvertTo-SecureString -String "YourPassword" -AsPlainText -Force)
• Restart the SQL Server service to force it to re-read the certificate store.

Step 4: Check Windows Cryptographic Provider and FIPS Policy

• Open Local Security Policy (secpol.msc) → Local Policies > Security Options.
• Ensure “System cryptography: Use FIPS compliant algorithms” is not unexpectedly enabled, as this blocks legacy CAPI-based certificates used by older SQL Server endpoints.
• If disabled previously, re-enable only after confirming SQL Server 2025 compatibility with your organization’s compliance requirements.

Step 5: Validate SQL Server Service Account Permissions

• Confirm the service account has Log on as a service rights via secpol.msc → User Rights Assignment.
• Ensure the account is not blocked by Group Policy changes pushed during the Windows Server 2025 upgrade.
• Restart the SQL Server and SQL Server Agent services after any permission changes.

Alternative or Verification Steps

• After applying fixes, test Service Broker connectivity using: SELECT * FROM sys.transmission_queue; to confirm no cryptographic failures are queued, and check the SQL Error Log for recurrence of Error 9650.
• If the issue persists, as a fallback, drop and recreate the mirroring/Service Broker endpoint with a freshly generated certificate using CREATE CERTIFICATE ... WITH SUBJECT = 'New Cert' instead of reusing the legacy one.

Troubleshooting Guide: Error 9651 – System Call Failed During Service Broker or Database Mirroring Operation

Why This Error Happens

Error 9651 occurs when the underlying Windows networking stack rejects or fails a system call that SQL Server 2025 issues to establish or maintain a Service Broker or Database Mirroring endpoint connection. This is typically caused by a blocked or conflicting TCP port, insufficient permissions on the SQL Server service account to bind to the network socket, or corrupted/missing network provider components (such as the Winsock catalog) on Windows Server 2025 or Windows 11. It can also surface when a firewall, antivirus, or third-party network filter driver intercepts the socket operation before SQL Server can complete it.

Step-by-Step Resolution Guide

Step 1: Identify the Exact System Error Returned

• Open the SQL Server Error Log via SQL Server Management Studio (SSMS) or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Locate the full text of Error 9651 — the %ls placeholder will contain a Windows system error code (e.g., “10048” for address already in use, or “10013” for permission denied).
• Cross-reference the embedded Windows error code using net helpmsg <code> in an elevated Command Prompt to get a plain-language description.

Step 2: Check for Port Conflicts on the Mirroring/Broker Endpoint

• Run netstat -ano | findstr <port_number> (commonly 5022 for mirroring or 4022 for Service Broker) to see if another process is already bound to that port.
• If a conflict exists, either stop the competing process or reassign the endpoint port using:

  ALTER ENDPOINT Mirroring STATE = STOPPED;
  ALTER ENDPOINT Mirroring AS TCP (LISTENER_PORT = 5023);
  ALTER ENDPOINT Mirroring STATE = STARTED;

• Update firewall rules accordingly via wf.msc or netsh advfirewall firewall add rule name="SQL Mirroring" dir=in action=allow protocol=TCP localport=5023.

Step 3: Verify Service Account Permissions and Socket Binding Rights

• Confirm the SQL Server service account (visible in SQL Server Configuration Manager) has “Log on as a service” rights and is not restricted by Group Policy.
• Check that the account is not blocked by Network access: Restrict clients allowed to make remote calls to SAM policies under secpol.msc.
• Restart the SQL Server service after any permission changes: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Repair the Winsock Catalog and Network Stack

• If the error code references a Winsock failure (e.g., 10050, 10051, 10060), reset the TCP/IP stack:

  netsh winsock reset
  netsh int ip reset

• Reboot the server after running these commands, as a stale or corrupted Winsock catalog frequently causes 9651 on freshly patched Windows Server 2025 builds.

Step 5: Disable Interfering Security Software Temporarily

• Temporarily disable third-party antivirus or endpoint protection network filters and retest the mirroring/broker session.
• If the issue resolves, add explicit exclusions for sqlservr.exe and the relevant TCP ports in the security software’s configuration.

Alternative or Verification Steps

• After applying fixes, re-establish the mirroring session or Service Broker conversation and confirm no 9651 entries appear in the Error Log for at least one full sync cycle.
• As an alternative, use sys.dm_tcp_listener_states to verify the endpoint state is “Ready” and check sys.service_broker_endpoints to ensure the endpoint configuration matches expected port and state values.

Troubleshooting Guide: SQL Server Error 9649 (SSPI Error in Service Broker)

Why This Error Happens

Error 9649 occurs when SQL Server’s Service Broker attempts to establish a secure dialog with another Service Broker instance but fails during the Kerberos/NTLM authentication handshake. This is typically caused by missing or misconfigured Service Principal Names (SPNs), a broken trust relationship between the client and target machine accounts in Active Directory, or the SQL Server service account lacking sufficient permissions to register or validate SPNs. It can also stem from clock skew between servers, DNS resolution failures, or firewall rules blocking the Kerberos ports (TCP/UDP 88) needed for ticket exchange.

Step-by-Step Resolution Guide

Step 1: Verify and Register the Service Principal Name (SPN)

• Open an elevated Command Prompt on the SQL Server host.
• Run setspn -L <DomainAccount> to list existing SPNs for the SQL Server service account.
• If the SPN is missing, register it manually using setspn -S MSSQLSvc/<FQDN>:<Port> <DomainAccount>.
• Ensure there are no duplicate SPNs registered under a different account using setspn -X.

Step 2: Check Active Directory Trust and Account Permissions

• Confirm the SQL Server service account has “Validated write to service principal name” permission delegated in Active Directory Users and Computers (ADUC).
• Verify the computer account or service account is not disabled or expired.
• Ensure both the local and remote SQL Server instances are part of the same domain or a trusted domain relationship exists.

Step 3: Synchronize System Clocks and Validate DNS

• Check the time difference between the two servers using w32tm /stripchart /computer:<RemoteServer>.
• Kerberos authentication fails if clock skew exceeds 5 minutes by default; resync using w32tm /resync on both nodes.
• Run nslookup <RemoteServerFQDN> from both machines to confirm forward and reverse DNS lookups resolve correctly.

Step 4: Review Windows Security Event Logs

• Open Event Viewer and navigate to Windows Logs > System and Windows Logs > Security.
• Filter for Event ID 4 (Kerberos) or LSASRV errors occurring at the same timestamp as the SQL Error 9649.
• Cross-reference the specific SSPI/Kerberos error code returned (e.g., KRBAPERR_MODIFIED) to pinpoint whether it is a password mismatch or ticket corruption issue.

Step 5: Restart Services and Clear Kerberos Ticket Cache

• Restart the SQL Server (MSSQLSERVER) service to force re-registration of the SPN if “Instant File Initialization” or domain changes were recently applied.
• Clear the local Kerberos ticket cache using klist purge on the client machine.
• Restart the Netlogon service on the Domain Controller if trust relationship errors persist.

Alternative or Verification Steps

• To verify the fix, initiate a Service Broker conversation between the two endpoints and monitor the sys.transmission_queue table for a drop in connection errors.
• If SSPI errors persist despite correct SPNs, bypass Kerberos by configuring Service Broker to use NTLM authentication or force a specific authentication mode via the ENCRYPTION clause in the CREATE ENDPOINT statement.

Troubleshooting Guide: SQL Server Error 9647 – Malformed Service Broker Message

Why This Error Happens

Error 9647 occurs when the Service Broker endpoint receives a message whose binary structure does not conform to the expected Service Broker protocol format, preventing SQL Server from parsing a required message attribute. This is typically caused by network-level packet corruption, a misconfigured or non-Service Broker application connecting to the Broker endpoint’s TCP port, or interference from a firewall/proxy that is fragmenting or altering packets in transit. In some cases, it can also stem from a mismatched or outdated network driver on Windows Server 2025 that mishandles the TCP stream.

Step-by-Step Resolution Guide

Step 1: Verify the Endpoint Port Is Exclusive to Service Broker

• Run SELECT name, port FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER'; to confirm the assigned port (default 4022).
• Use netstat -ano | findstr <port> in PowerShell to check if any non-SQL process is bound to or communicating on that port.
• Terminate or reconfigure any rogue application found occupying the Service Broker port.

Step 2: Inspect Network Path and Firewall Rules

• Open Windows Defender Firewall with Advanced Security and confirm an inbound rule exists allowing TCP traffic on the Service Broker port.
• Check for intermediate network devices (load balancers, proxies) that may be fragmenting packets; Service Broker traffic should bypass deep packet inspection.
• Temporarily disable third-party firewall/antivirus software to rule out packet tampering, then re-test the connection.

Step 3: Validate Certificate and Encryption Configuration

• Run SELECT * FROM sys.service_broker_endpoints; to confirm encryption algorithm settings match on both ends of the dialog.
• If using certificates, verify they haven’t expired: SELECT name, expiry_date FROM sys.certificates;
• Re-issue or renew any expired certificates used for Service Broker authentication.

Step 4: Update Network Drivers and SQL Server Cumulative Updates

• Navigate to Device Manager > Network Adapters, right-click the active adapter, and select “Update driver” to ensure the latest Windows Server 2025 certified driver is installed.
• Apply the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or Microsoft Update Catalog.
• Restart the SQL Server service after updates: Restart-Service -Name MSSQLSERVER.

Step 5: Enable Service Broker Diagnostics for Root Cause Confirmation

• Run ALTER DATABASE <DBName> SET ENABLE_BROKER; if Broker is unexpectedly disabled.
• Check the SQL Server Error Log and sys.transmission_queue for repeated failed message entries.
• Use SQL Server Profiler or Extended Events with the broker_message_classify event to trace the malformed packet source.

Alternative or Verification Steps

• To verify the fix, send a test message between two Service Broker services using SEND ON CONVERSATION and confirm it arrives without triggering Error 9647 in the SQL Server Error Log.
• If the issue persists, isolate the database by restoring it to a different instance on an isolated network segment — if the error disappears, the root cause is network-based rather than database corruption.

Troubleshooting Guide: SQL Server Error 9648 — “The queue ‘%.*ls’ has been enabled for activation”

Why This Error Happens

This message is actually an informational message, not a critical failure — it is logged whenever Service Broker successfully enables activation on a specified queue, typically after a server restart, a queue ALTER operation, or an availability group failover. However, administrators often flag it as an “error” because it appears repeatedly in the SQL Server error log, which usually indicates that activation is being toggled on and off due to configuration drift, a stored procedure activation failure, or the queue being altered via scripts/ORM tools repeatedly. In rare cases, excessive logging of this event points to a poison message loop or an unstable activation stored procedure causing SQL Server to disable and re-enable the queue automatically.

Step-by-Step Resolution Guide

Step 1: Confirm Whether This Is Actually a Problem

• Open the SQL Server Error Log via SSMS: Management > SQL Server Logs > Current.
• Search for Event ID 9648 and check the timestamp pattern — if it only appears once after a service restart or failover, this is expected behavior and not an error.
• Cross-reference with Event ID 9675 (“queue has been disabled”) to see if the queue is flapping (enabled/disabled repeatedly).

Step 2: Inspect the Queue and Activation Procedure Configuration

• Run the following query to check current queue status and activation settings:

SELECT name, is_activation_enabled, activation_procedure, 
       max_queue_readers, execute_as_principal_id
FROM sys.service_queues
WHERE name = 'YourQueueName';

• Verify that activation_procedure points to a valid, existing stored procedure.
• Check execute_as_principal_id to confirm the activation procedure’s execution context has correct permissions on the database and EXECUTE rights.

Step 3: Check for Poison Message Handling Issues

• Query the sys.transmission_queue and target queue for a buildup of messages, which can trigger repeated enable/disable cycles:

SELECT * FROM sys.transmission_queue;
SELECT COUNT(*) FROM YourQueueName;

• If poison messages are detected (5 consecutive transaction rollbacks), either fix the activation stored procedure’s error handling (wrap logic in TRY/CATCH) or manually clear the poisoned message using RECEIVE with explicit transaction control.
• Rebuild the activation procedure logic to ensure it commits or rolls back cleanly without causing SQL Server to auto-disable the queue.

Step 4: Validate Service Broker and Database Settings

• Confirm Service Broker is enabled on the database:

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

• If disabled, enable it with:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• On Windows Server 2025 / Windows 11, ensure no Group Policy or Windows Firewall change is interrupting the SQL Server service account’s permissions on the database files (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).

Step 5: Re-enable Activation Manually and Monitor

• If activation was manually disabled due to troubleshooting, re-enable it explicitly:

ALTER QUEUE YourQueueName WITH ACTIVATION (STATUS = ON);

• Monitor the error log for the next 24 hours to confirm the 9648 message does not reappear in a tight, repeating loop.

Alternative or Verification Steps

• Verify the fix: Run SELECT is_activation_enabled FROM sys.service_queues WHERE name = 'YourQueueName'; — a consistent value of 1 without flapping confirms stability.
• Alternative solution: If the activation procedure continues to fail, consider implementing a dedicated error-logging table within the procedure and temporarily set max_queue_readers = 0 to halt activation while you debug the underlying stored procedure logic offline.

Troubleshooting Guide: SQL Server Error 9646 – “An error occurred in the timer event cache”

Why This Error Happens

Error 9646 typically occurs when SQL Server’s internal scheduler mechanism (SOS Scheduler) encounters a corrupted or inconsistent state in its timer event cache, often due to memory pressure or a damaged tempdb system database. This can also be triggered by outdated or incompatible SQL Server cumulative updates interacting poorly with the Windows Server 2025 kernel scheduling APIs. In some cases, antivirus or endpoint protection software intercepting SQL Server’s internal thread timer calls can corrupt the cache state as well.

Step-by-Step Resolution Guide

Step 1: Verify and Rebuild tempdb

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check tempdb file health: DBCC CHECKDB (tempdb) WITH NO_INFOMSGS;
• If corruption is found, restart the SQL Server service to force tempdb to rebuild automatically, since tempdb is recreated on every service restart.
• Confirm file paths are correct in SQL Server Configuration Manager > SQL Server Services > Startup Parameters.

Step 2: Apply the Latest Cumulative Update (CU)

• Navigate to the Microsoft Update Catalog and download the latest CU for SQL Server 2025.
• Check your current build number by running: SELECT @@VERSION;
• Install the CU package (e.g., SQLServer2025-KB5XXXXXX-x64.exe) and reboot Windows Server 2025 if prompted.
• This resolves known scheduler-timer race conditions patched in recent builds.

Step 3: Exclude SQL Server Processes from Antivirus Scanning

• Open Windows Security or your third-party AV console.
• Add exclusions for sqlservr.exe located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\.
• Exclude the data directories: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Restart the SQL Server service to apply changes cleanly.

Step 4: Review SQL Server Error Log for Memory Pressure

• Open SQL Server Error Log via SSMS under Management > SQL Server Logs.
• Look for related entries like “Failed Virtual Allocate Bytes” or “RESOURCE_SEMAPHORE” near the timestamp of Error 9646.
• If memory pressure is confirmed, adjust the max server memory setting via: EXEC sp_configure 'max server memory', 16384; RECONFIGURE; (adjust value based on available RAM).

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server Error Log for 24–48 hours to confirm Error 9646 does not recur during peak load periods.
• If the issue persists, run DBCC FREESYSTEMCACHE('ALL') to manually clear cached scheduler data, or consider failing over to a secondary replica (if using Always On Availability Groups) while performing a full tempdb and instance restart.

Troubleshooting Guide: SQL Server Error 9644 — Service Broker Message Dispatcher Failure

Why This Error Happens

Error 9644 occurs when the Service Broker’s internal message dispatcher encounters an unhandled exception while attempting to deliver a message to a queue, often due to a corrupted message payload, a disabled or improperly configured Service Broker endpoint, or insufficient permissions on the target database. This can also surface when the master key or certificates used for Service Broker encryption are missing, inaccessible, or have expired, preventing the dispatcher from decrypting and routing messages correctly. In some cases, network instability between dialog endpoints in a multi-server broker conversation triggers this dispatcher-level failure.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check the broker status: SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• If is_broker_enabled returns 0, enable it using: ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Ensure no active connections are blocking this change by running the command in single-user mode if necessary: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Step 2: Inspect and Repair Corrupted Queue Messages

• Query the transmission queue for stuck or malformed messages: SELECT * FROM sys.transmission_queue;
• Look for entries with non-null transmission_status values, which indicate delivery failures.
• If corrupted messages are found, clear the poison message queue using: ALTER QUEUE YourQueueName WITH STATUS = OFF; ALTER QUEUE YourQueueName WITH STATUS = ON;

Step 3: Validate Master Key and Certificate Configuration

• Confirm the database master key exists and is open: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';
• Check certificate expiration dates tied to Service Broker: SELECT name, expiry_date FROM sys.certificates;
• Re-issue or renew any expired certificates used in dialog security, then restart the SQL Server service via services.msc on Windows Server 2025 or Windows 11.

Step 4: Review SQL Server Error Logs and Windows Event Viewer

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to locate the detailed stack trace tied to Error 9644.
• Cross-reference timestamps with Windows Event Viewer under Windows Logs > Application to identify correlating system-level faults (e.g., network timeouts or disk I/O errors).
• Note the specific %i error number returned in the message, as this maps to a more granular internal SQL Server error code for further diagnosis.

Alternative or Verification Steps

• After applying fixes, send a test message through the affected Service Broker conversation and confirm it appears in the target queue using SELECT * FROM YourQueueName; without triggering a new 9644 error in the log.
• If the issue persists, drop and recreate the Service Broker route and conversation endpoint entirely using CREATE ROUTE and CREATE SERVICE, as a corrupted routing table entry is a common root cause that configuration changes alone won’t resolve.

Troubleshooting Guide: SQL Server Error 9645 – Service Broker Manager Error

Why This Error Happens

Error 9645 occurs when the Service Broker Manager fails to initialize properly during SQL Server startup or operation, typically due to corrupted Service Broker metadata, a GUID conflict in the servicebrokerguid value stored in the database, or insufficient permissions on the master database. This can also arise when multiple databases on the same instance have conflicting Service Broker identifiers after a database restore or attach operation, or when the endpoint configuration used for Service Broker message exchange is damaged or blocked. In some cases, corrupted system catalog views related to Service Broker (sys.servicebrokerendpoints, sys.databases) contribute to this failure on Windows Server 2025 and Windows 11 hosts.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status on the Database

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check broker status:

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

• If is_broker_enabled returns 0, this confirms Service Broker is disabled or misconfigured.

Step 2: Resolve GUID Conflicts After Restore/Attach

• If the database was recently restored or attached, a duplicate servicebrokerguid is often the root cause.
• Run the following command to assign a new, unique GUID:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER;

• Alternatively, if you need to preserve existing conversations, use:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 3: Check and Repair Service Broker Endpoint Configuration

• Navigate to SSMS > Security > Endpoints and confirm a Service Broker endpoint exists and is started.
• Verify the TCP port (default 4022) is not blocked by Windows Defender Firewall:
• Open wf.msc on Windows Server 2025 / Windows 11.
• Create an inbound rule allowing TCP port 4022 for the sqlservr.exe process.
• If the endpoint is missing, recreate it using:

  CREATE ENDPOINT ServiceBrokerEndpoint
  STATE = STARTED
  AS TCP (LISTENER_PORT = 4022)
  FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);

Step 4: Restart the SQL Server Service and Validate Permissions

• Ensure the SQL Server service account has appropriate permissions on the master database and the instance’s data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Restart the SQL Server service via services.msc or run:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Check the SQL Server Error Log immediately after restart for recurrence of Error 9645.

Alternative or Verification Steps

• To verify the fix, query sys.databases again and confirm is_broker_enabled = 1, then check the SQL Server Error Log (ERRORLOG file in the MSSQL\Log directory) for absence of Error 9645 messages.
• If the error persists, consider running DBCC CHECKDB against the affected database to rule out underlying corruption, or as a last resort, back up the database and perform a clean detach/attach cycle to force regeneration of Service Broker metadata.

Troubleshooting Guide: Error 9642 – Service Broker/Database Mirroring Transport Connection Endpoint Error

Why This Error Happens

Error 9642 typically occurs when the TCP transport layer used by Service Broker or Database Mirroring encounters a disruption while establishing or maintaining a connection endpoint. This is commonly caused by a blocked or conflicting port in the Windows Firewall, an expired or mismatched SSL/TLS certificate used for endpoint authentication, or network instability between the mirroring/broker partners. In some cases, the issue stems from incorrect endpoint configuration (wrong state, protocol, or authentication mode) on one of the SQL Server instances.

Step-by-Step Resolution Guide

Step 1: Verify Endpoint Configuration and State

• Run SELECT * FROM sys.database_mirroring_endpoints; or SELECT * FROM sys.service_broker_endpoints; on both instances to confirm the endpoint exists and is in a STARTED state.
• If the state shows STOPPED or DISABLED, run ALTER ENDPOINT [EndpointName] STATE = STARTED;.
• Confirm the correct port (default 5022) is assigned using SELECT port FROM sys.tcp_endpoints;.

Step 2: Check Windows Firewall and Network Connectivity

• Open Windows Defender Firewall with Advanced Security on Windows Server 2025 / Windows 11.
• Create or verify an Inbound Rule allowing TCP traffic on port 5022 (or your custom mirroring/broker port).
• Test connectivity between partner servers using Test-NetConnection -ComputerName <PartnerServer> -Port 5022 in PowerShell.
• Ensure no intermediate network firewalls or NSGs (if on Azure VMs) are blocking the connection.

Step 3: Validate SSL/TLS Certificates Used for Authentication

• Check certificate expiration dates using SELECT name, expiry_date FROM sys.certificates;.
• If a certificate has expired or is mismatched between partners, regenerate and re-exchange certificates using CREATE CERTIFICATE and BACKUP CERTIFICATE.
• Re-import the updated certificate on the partner server and recreate the endpoint authentication using:
  ALTER ENDPOINT [EndpointName] FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [CertName]);

Step 4: Review SQL Server Error Logs and Extended Events

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for related entries near Error 9642, such as network timeout messages or certificate validation failures.
• Enable an Extended Events session targeting the broker_login or mirroring_state_change events for deeper diagnostics.

Step 5: Restart the Endpoint and SQL Server Service

• Run ALTER ENDPOINT [EndpointName] STATE = STOPPED; followed by ALTER ENDPOINT [EndpointName] STATE = STARTED; to reset the connection.
• If the issue persists, restart the SQL Server (MSSQLSERVER) service via Services.msc or Restart-Service MSSQLSERVER in PowerShell.

Alternative or Verification Steps

• Verify the fix by checking sys.dm_broker_connections and sys.database_mirroring DMVs to confirm active, healthy connections without error states.
• If the issue persists after certificate and firewall checks, consider recreating the Mirroring/Broker endpoints entirely using DROP ENDPOINT followed by a fresh CREATE ENDPOINT statement with validated authentication settings.

Troubleshooting Guide: Error 9643 — Service Broker/Database Mirroring Transport Manager Error

Why This Error Happens

Error 9643 typically occurs when the Service Broker or Database Mirroring endpoint fails to initialize correctly due to a TCP port conflict, certificate authentication failure, or an invalid endpoint configuration. This often happens after a Windows Server 2025 update resets firewall rules, or when the SQL Server service account lacks the correct permissions to bind to the configured endpoint port. Corrupted or expired certificates used for endpoint encryption can also trigger this transport manager failure, as can mismatched SQL Server 2025 TLS 1.3 negotiation settings between mirrored instances.

Step-by-Step Resolution Guide

Step 1: Verify the Endpoint Configuration and Status

• Open SQL Server Management Studio (SSMS) and run:
  SELECT * FROM sys.database_mirroring_endpoints;
• Confirm the endpoint state_desc shows STARTED and not STOPPED or DISABLED.
• If stopped, restart it manually using:
  ALTER ENDPOINT [Mirroring_Endpoint] STATE = STARTED;

Step 2: Check for Port Conflicts and Firewall Blocking

• Run netstat -ano | findstr <port_number> in an elevated Command Prompt (default mirroring port is often 5022).
• Identify if another process or SQL instance is already bound to that port.
• Open Windows Defender Firewall with Advanced Security and ensure an inbound rule exists allowing TCP traffic on the mirroring port for sqlservr.exe.
• Re-create the firewall rule if necessary:
  New-NetFirewallRule -DisplayName "SQL Mirroring" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow

Step 3: Validate Certificates and Service Account Permissions

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ (adjust version folder for SQL Server 2025) to confirm certificate files are present and not corrupted.
• Run the following to check certificate expiration:
  SELECT name, expiry_date FROM sys.certificates;
• Ensure the SQL Server service account has Log on as a Service rights and read access to the certificate private key via certlm.msc.
• If certificates are expired or invalid, re-issue them using CREATE CERTIFICATE and re-bind them to the endpoint.

Step 4: Restart the SQL Server and Service Broker Services

• Open Services.msc and restart:
• SQL Server (MSSQLSERVER)
• SQL Server Agent (MSSQLSERVER)
• Alternatively, use PowerShell:
  Restart-Service -Name 'MSSQLSERVER' -Force
• Confirm Service Broker is enabled on the database:
  SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDBName';

Alternative or Verification Steps

• Check the SQL Server Error Log (via SSMS → Management → SQL Server Logs) for follow-up messages confirming the endpoint successfully restarted without further 9643 entries.
• If the issue persists, drop and recreate the mirroring endpoint entirely using DROP ENDPOINT followed by a fresh CREATE ENDPOINT statement with updated certificate bindings.

Troubleshooting Guide: SQL Server Error 9641 — Cryptographic Operation Failed

Why This Error Happens

Error 9641 occurs when SQL Server’s cryptographic provider (either the built-in Windows CNG/CAPI provider or an Extensible Key Management [EKM] module) fails to perform an encryption, decryption, or key-signing operation. This is typically caused by a corrupted or inaccessible Database Master Key (DMK), Service Master Key (SMK), or certificate, insufficient permissions on the key container in the Windows profile, or an underlying issue with the operating system’s cryptographic services (such as a corrupted key store or a disabled/misconfigured Cryptographic Services provider). It can also stem from a failed Windows Update or FIPS policy change that invalidated existing certificate-based encryption chains.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server and Windows Event Logs

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs to locate the exact timestamp of Error 9641.
• Cross-reference with the Windows Event Viewer under Windows Logs > Application and System for related CryptoAPI (Event ID 11) or Schannel errors.
• Note any associated error (e.g., missing certificate thumbprint, key container not found) as this narrows down whether the issue is DMK, SMK, or a specific certificate.

Step 2: Verify and Repair the Service Master Key (SMK) and Database Master Key (DMK)

• Run the following in a new query window to check SMK integrity:

  SELECT * FROM sys.key_encryptions;

• If the SMK is suspected corrupted, restore it from a known good backup:

  RESTORE SERVICE MASTER KEY FROM FILE = 'C:\Backups\service_master_key'
  DECRYPTION BY PASSWORD = 'YourStrongPassword';

• For a corrupted DMK on a specific database, open it manually to force regeneration:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';
  ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'YourStrongPassword';

Step 3: Check File System Permissions on the Key Container

• Confirm the SQL Server service account (visible under SQL Server Configuration Manager) has read/write access to:

  C:\Users\<ServiceAccount>\AppData\Roaming\Microsoft\Crypto\RSA\
  C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\

• Right-click the folder, select Properties > Security, and ensure the service account has Full Control.
• Restart the SQL Server service after applying permission changes via services.msc or:

  Restart-Service -Name "MSSQLSERVER" -Force

Step 4: Validate Windows Cryptographic Services and Certificates

• Ensure the Cryptographic Services Windows service is running (services.msc > Cryptographic Services > Status: Running).
• If using EKM or TDE with a certificate, validate the certificate hasn’t expired:

  SELECT name, expiry_date FROM sys.certificates;

• Re-import or renew any expired certificate, then re-encrypt the DMK/SMK referencing it.

Step 5: Apply Latest Cumulative Updates and Reboot

• Confirm SQL Server 2025 is patched to the latest Cumulative Update (CU) via SSMS > Help > About.
• Apply pending Windows Updates on Windows Server 2025 / Windows 11, particularly security and .NET Framework patches affecting cryptographic libraries.
• Perform a full system reboot to ensure the updated crypto providers are loaded cleanly.

Alternative or Verification Steps

• Run DBCC CHECKDB and attempt a test backup/restore cycle with encryption enabled to confirm the cryptographic chain is now functioning without triggering Error 9641.
• If the error persists, isolate the issue by temporarily disabling TDE or EKM-dependent features (if non-production) to confirm whether the fault lies within SQL Server’s internal key management or an external HSM/EKM provider.

Troubleshooting Guide: SQL Server Error 9639 – “A Password Was Supplied and the PVK File Is Not Encrypted”

Why This Error Happens

This error occurs when SQL Server attempts to load a private key (.pvk) file using a DECRYPTION BY PASSWORD clause, but the target .pvk file was actually exported or generated in an unencrypted state. SQL Server detects a mismatch between the command syntax (which implies encryption) and the actual file header, which lacks the expected encryption metadata. This commonly happens after certificates are exported using tools like certutil or PowerShell’s Export-PfxCertificate without properly specifying a private key encryption password, or when a .pvk file is corrupted/truncated during transfer between Windows Server 2025 and other systems.

Step-by-Step Resolution Guide

Step 1: Verify the Encryption State of the PVK File

• Check how the .pvk file was originally generated (e.g., via certutil -exportPFX or OpenSSL).
• Run certutil -dump <filename>.pvk in an elevated Command Prompt on Windows Server 2025 to inspect file metadata.
• Look for explicit indicators of “Encrypted” vs “Not Encrypted” in the output — if it says unencrypted, this confirms the root cause.

Step 2: Re-run the T-SQL Command Without the Password Clause

• If the .pvk is confirmed unencrypted, remove the DECRYPTION BY PASSWORD = '<password>' clause from your CREATE CERTIFICATE or BACKUP CERTIFICATE statement.
• Example corrected syntax:

  CREATE CERTIFICATE MyCert
  FROM FILE = 'C:\Certs\MyCert.cer'
  WITH PRIVATE KEY (FILE = 'C:\Certs\MyCert.pvk');

• Execute this in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.

Step 3: Re-export the PVK File With Proper Encryption (If Password Protection Is Required)

• If you actually need password protection on the key, regenerate the .pvk file using a tool that supports PVK encryption, such as pvk2pfx or OpenSSL with explicit encryption flags.
• Example using OpenSSL:

  openssl rsa -in private.key -aes256 -out encrypted_private.pvk

• Ensure the new file is placed in a secure directory (e.g., C:\Certs\Secure\) with restricted NTFS permissions (SYSTEM and SQL Server service account only).

Step 4: Validate File Integrity and Permissions

• Confirm the SQL Server service account has Read access to the folder containing the .pvk file.
• Check for file corruption by comparing file size/hash against the original source (use Get-FileHash in PowerShell).
• Re-transfer the file using a lossless method (e.g., SMB share or secure FTP) if corruption is suspected.

Alternative or Verification Steps

• Re-run the original CREATE CERTIFICATE or BACKUP CERTIFICATE statement in SSMS and confirm no Error 9639 is raised, then query sys.certificates to verify the certificate was loaded successfully.
• As an alternative, if the password requirement cannot be removed, regenerate the entire certificate/key pair with explicit AES-based encryption using certreq or a trusted CA tool, ensuring the encryption flag is set at creation time rather than retrofitted later.

Troubleshooting Guide: SQL Server Error 9640

Why This Error Happens

Error 9640 occurs when SQL Server’s filestream or I/O subsystem encounters an unexpected operating system-level error while attempting to read from or write to disk. This is typically caused by underlying storage issues such as corrupted NTFS volumes, insufficient disk permissions for the SQL Server service account, antivirus software locking database files, or a failing/misconfigured storage controller. In some cases, this error also surfaces when Windows Server 2025’s updated storage driver stack conflicts with older SQL Server I/O call patterns.

Step-by-Step Resolution Guide

Step 1: Verify Disk Health and File System Integrity

• Open Command Prompt as Administrator and run chkdsk C: /f /r (replace C: with the actual data drive letter).
• Check Windows Event Viewer under Windows Logs > System for Disk or Ntfs warnings correlating with the timestamp of Error 9640.
• If using Storage Spaces or a SAN, verify the volume status in Server Manager > File and Storage Services.

Step 2: Confirm SQL Server Service Account Permissions

• Navigate to the data/log file directory (e.g., D:\SQLData\ or D:\SQLLogs\).
• Right-click the folder, select Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Re-apply permissions if recently changed by running:
  icacls "D:\SQLData" /grant "NT Service\MSSQLSERVER":(OI)(CI)F /T

Step 3: Exclude SQL Server Files from Antivirus/Defender Scanning

• Open Windows Security > Virus & threat protection > Manage settings > Add or remove exclusions.
• Add exclusions for the .mdf, .ldf, .ndf file paths and the sqlservr.exe process.
• Restart the SQL Server service via services.msc after applying exclusions.

Step 4: Update Storage and HBA Drivers

• Check Device Manager for outdated storage controller or HBA (Host Bus Adapter) drivers.
• Download the latest Windows Server 2025–certified drivers from the hardware vendor (e.g., Dell, HPE, Broadcom).
• Reboot the server after installation and monitor the SQL Server error log (ERRORLOG in the Log folder) for recurrence.

Step 5: Run SQL Server Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SQL Server Management Studio.
• Review output for corruption flags that may indicate the OS error has already damaged database pages.

Alternative or Verification Steps

• To verify the fix, reproduce the original operation (e.g., backup, restore, or large write transaction) and monitor the SQL Server ERRORLOG for a clean completion without Error 9640.
• If the issue persists after driver and permission fixes, restore the affected database from the last known-good backup to a separate volume and run DBCC CHECKDB to rule out pre-existing corruption.

Troubleshooting Guide: Error 9638 – Certificate/Private Key File Size Too Large

Why This Error Happens

Error 9638 in SQL Server 2025 occurs when the .cer or .pvk file being loaded via CREATE CERTIFICATE or BACKUP CERTIFICATE exceeds the maximum size SQL Server’s cryptographic API will accept, typically because the file was exported with an unsupported key length, corrupted during transfer, or accidentally saved in the wrong format (e.g., PEM bundle instead of raw DER/PVK binary). This can also happen if the certificate file contains an entire certificate chain or extra metadata rather than a single certificate, inflating its size beyond SQL Server’s internal buffer limits. In some cases, file corruption from an incomplete FTP/SCP transfer on Windows Server 2025 or Windows 11 introduces padding bytes that push the file past the accepted threshold.

Step-by-Step Resolution Guide

Step 1: Verify the File Format and Integrity

• Confirm the .cer file is in DER-encoded binary format, not Base64/PEM, by opening it in a hex editor — PEM files start with -----BEGIN CERTIFICATE-----.
• Run certutil -dump yourfile.cer from an elevated Command Prompt to check if the file structure is valid and not duplicated or chained.
• Re-transfer the file using binary mode (not ASCII mode) if it came from an SCP/FTP source, as mode mismatches commonly bloat file size.

Step 2: Re-Export the Certificate with Correct Parameters

• Open the Certificate Manager (certlm.msc) on the source machine and locate the original certificate.
• Right-click the certificate, select All Tasks > Export, and choose DER encoded binary X.509 (.CER) — avoid selecting “Include all certificates in the certification path” as this bundles the chain and increases file size.
• For .pvk files, use pvk2pfx.exe or OpenSSL (openssl rsa conversion) to regenerate a clean private key file without extra chain data.

Step 3: Recreate the Certificate Directly in SQL Server

• Instead of importing an oversized external file, regenerate the certificate using T-SQL to avoid file-size validation issues entirely:

CREATE CERTIFICATE MyCert
WITH SUBJECT = 'New Certificate for Backup Encryption';

• If an external key is mandatory, use BACKUP CERTIFICATE MyCert TO FILE = 'C:\Certs\MyCert.cer' WITH PRIVATE KEY (FILE = 'C:\Certs\MyCert.pvk', ENCRYPTION BY PASSWORD = 'StrongPassword123!') to generate a properly sized pair.

Step 4: Check SQL Server Buffer and Registry Limits

• Verify the TokenAuthenticatedUser and crypto provider settings haven’t been altered via Group Policy on Windows Server 2025, which can artificially cap buffer sizes for certificate imports.
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) after any file or policy changes to clear cached crypto handles.

Alternative or Verification Steps

• Confirm the fix by re-running the CREATE CERTIFICATE ... FROM FILE = 'C:\Certs\MyCert.cer' statement — if it completes without Error 9638, the file size issue is resolved.
• As an alternative, use PowerShell’s New-SelfSignedCertificate cmdlet to generate a fresh, correctly-sized certificate pair directly compatible with SQL Server 2025’s import requirements.

Troubleshooting Guide: SQL Server Error 9636 – “The Requested Object Was Not Found”

Why This Error Happens

Error 9636 typically occurs when SQL Server 2025 attempts to reference a database object (such as a table, index, partition function, or statistics object) that has been dropped, renamed, or corrupted at the metadata level while another process still holds a reference to it. This frequently happens due to stale query plan cache entries, orphaned system catalog views, or concurrent DDL operations (like ALTER TABLE or index rebuilds) running against the same object during a transaction. In some cases, it also surfaces when a database was restored or migrated from an older compatibility level and system metadata was not fully synchronized.

Step-by-Step Resolution Guide

Step 1: Clear the Procedure Cache and Verify Object Existence

• Run DBCC FREEPROCCACHE to eliminate stale execution plans referencing the missing object.
• Query sys.objects and sys.indexes directly to confirm whether the object truly exists:

  SELECT * FROM sys.objects WHERE name = 'YourObjectName';

• If the object is missing, check recent DDL activity in the Default Trace or Extended Events session.

Step 2: Run Consistency Checks on the Database

• Open SQL Server Management Studio (SSMS) 21 or use PowerShell/sqlcmd on Windows Server 2025.
• Execute:

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

• Review output for allocation or metadata corruption tied to the missing object ID.
• If corruption is confirmed, note the object ID and proceed to repair options (REPAIR_REBUILD as a last resort, in a maintenance window).

Step 3: Rebuild Affected Indexes or Statistics

• Identify any index or statistics objects tied to the error using the object ID from Step 2.
• Rebuild indexes to force metadata refresh:

  ALTER INDEX ALL ON YourTableName REBUILD;

• Update statistics manually to eliminate orphaned references:

  UPDATE STATISTICS YourTableName WITH FULLSCAN;

Step 4: Restart the SQL Server Service (If Issue Persists)

• On Windows Server 2025/Windows 11, open Services.msc.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to clear any in-memory metadata locks causing the stale reference.
• Confirm the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) shows a clean startup with no further 9636 entries.

Alternative or Verification Steps

• Verify the fix: Re-run the original query or application process that triggered Error 9636 and confirm it completes without exceptions; also re-run DBCC CHECKDB to ensure zero allocation errors.
• Alternative solution: If the error persists after a rebuild, restore the affected database from the last known good backup taken before the metadata corruption occurred, then reapply transaction logs if point-in-time recovery is required.

Troubleshooting Guide: SQL Server Error 9637 — “The passed in serialized object is incorrectly encoded”

Why This Error Happens

This error typically occurs when SQL Server 2025 attempts to deserialize a binary object—such as a serialized execution plan, CLR type, spatial data object, or Service Broker message—that has been corrupted or truncated during storage or transmission. It can also arise from encoding mismatches when data is passed between mixed-version SQL Server instances or through improperly configured linked servers. Additionally, corrupted system catalog entries or damaged database pages storing serialized metadata can trigger this error during read operations.

Step-by-Step Resolution Guide

Step 1: Run Database Consistency Checks

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify corruption in pages storing serialized objects.
• Review the output for allocation or consistency errors tied to specific object IDs.

Step 2: Validate CLR and Spatial Data Objects

• Check if the error originates from CLR integration by running SELECT * FROM sys.assemblies; to confirm assembly integrity.
• If spatial data types (geometry/geography) are involved, validate the data using SELECT *, YourColumn.STIsValid() FROM YourTable;
• Rebuild or re-import any assemblies/data flagged as invalid using ALTER ASSEMBLY or re-inserting clean source data.

Step 3: Inspect Service Broker and Linked Server Configurations

• If using Service Broker, run SELECT * FROM sys.transmission_queue; to check for malformed messages stuck in the queue.
• Clear corrupted messages using ALTER QUEUE commands or purge the transmission queue if safe to do so.
• For linked servers, verify provider settings under Server Objects > Linked Servers > Providers in SSMS, ensuring “Enable Promotion of Distributed Transactions” and collation compatibility settings match source/target servers.

Step 4: Restore From a Known Good Backup

• If corruption is confirmed via DBCC CHECKDB, restore the affected database or table from the most recent clean backup.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourBackup.bak' WITH REPLACE;
• Re-apply transaction logs if point-in-time recovery is required.

Alternative or Verification Steps

• After applying fixes, re-run the original query or operation that triggered Error 9637 and confirm it completes without exceptions; also re-run DBCC CHECKDB to ensure zero allocation/consistency errors remain.
• If the issue persists, enable SQL Server extended events (sp_configure 'show advanced options') to capture the exact serialized payload at failure time, then escalate to Microsoft Support with the captured binary dump for deeper diagnostics.

Troubleshooting Guide: SQL Server Error 9635 – “The Certificate Is Not Valid at This Point in Time”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to use a certificate (for Transparent Data Encryption, Service Broker, Database Mirroring, or Always Encrypted) whose validity period has either not yet started or has already expired relative to the system clock. It is frequently caused by a mismatch between the server’s system time/timezone and the certificate’s “Valid From” / “Valid To” timestamps, especially after a VM snapshot restore or migration. Corrupted certificate metadata in the master database or an incorrectly generated certificate with a future start date can also trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify System Clock and Timezone Accuracy

• Open an elevated PowerShell prompt and run w32tm /query /status to confirm the server is synced to a reliable NTP source.
• Check Settings > Time & Language on Windows 11, or Control Panel > Date and Time on Windows Server 2025, to confirm the correct timezone is set.
• If drift is detected, force a resync: w32tm /resync /force.
• Restart the SQL Server service after correcting time: Restart-Service MSSQLSERVER.

Step 2: Inspect the Certificate’s Validity Window

• Connect via SSMS and run:

  SELECT name, start_date, expiry_date, pvt_key_encryption_type_desc
  FROM sys.certificates;

• Compare start_date and expiry_date against the current server date/time.
• If the certificate is expired, you must create a new certificate rather than attempting to extend validity (SQL Server certificates cannot be renewed in place).
• If the certificate’s start date is in the future, this usually indicates a timezone miscalculation during certificate creation — regenerate it with an explicit START_DATE matching current UTC time.

Step 3: Regenerate or Re-Import the Certificate

• Back up the current certificate and private key before making changes:

  BACKUP CERTIFICATE YourCertName
  TO FILE = 'C:\Certs\YourCertName.cer'
  WITH PRIVATE KEY (
      FILE = 'C:\Certs\YourCertName.pvk',
      ENCRYPTION BY PASSWORD = 'StrongPassword!123'
  );

• Create a new certificate with a valid date range:

  CREATE CERTIFICATE YourCertName
  WITH SUBJECT = 'TDE Certificate',
  START_DATE = '2025-01-01',
  EXPIRY_DATE = '2027-01-01';

• If using TDE, re-encrypt the database master key or database encryption key to reference the new certificate.

Step 4: Re-Apply Certificate to Affected Feature

• For TDE: Alter the database encryption key to use the new certificate:

  ALTER DATABASE ENCRYPTION KEY
  ENCRYPTION BY SERVER CERTIFICATE YourCertName;

• For Service Broker or Database Mirroring endpoints, re-run the ALTER ENDPOINT or ALTER SERVICE statements referencing the updated certificate.
• Restart the SQL Server service to ensure the certificate binding takes effect cleanly.

Alternative or Verification Steps

• Confirm the fix by querying sys.certificates again and checking SQL Server Error Log (EXEC sp_readerrorlog;) for any further 9635 entries after a restart.
• If the issue persists, restore the master database and affected certificate from a known-good backup taken before the clock/timezone discrepancy occurred.

Troubleshooting Guide: SQL Server Error 9633 – “The Passed in Name is Too Long”

Why This Error Happens

Error 9633 occurs when an identifier passed to a SQL Server system function or metadata operation exceeds the maximum allowed length, typically 128 characters for standard identifiers. This is commonly triggered by overly long object names, deeply nested schema paths, or automated scripts that concatenate names dynamically without validation. It can also surface when linked server queries or third-party tools generate temporary object names that exceed SQL Server’s internal naming constraints.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object or Query

• Review the error message details in SQL Server Management Studio (SSMS) or the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Use sys.dm_exec_sql_text and sys.dm_exec_requests to trace the exact query that triggered the error.
• Check if the issue originates from a dynamically generated name, such as a temp table, constraint, or index name built via string concatenation.

Step 2: Validate Identifier Length Against SQL Server Limits

• Confirm the object name does not exceed 128 characters (or 116 characters for local temporary table names prefixed with #).
• Run a quick validation query:

  SELECT LEN('YourObjectNameHere') AS NameLength;

• If the name is dynamically generated (e.g., in a stored procedure or ETL job), add a truncation or hashing mechanism to shorten names programmatically.

Step 3: Correct the Script or Application Logic

• Modify any T-SQL scripts, SSIS packages, or application code that constructs object names to enforce a maximum length check before execution.
• For dynamic SQL, implement a safeguard such as:

  IF LEN(@ObjectName) > 128
      SET @ObjectName = LEFT(@ObjectName, 120) + CONVERT(VARCHAR(8), CHECKSUM(@ObjectName));

• Update any linked server or replication configurations that may be passing overly long qualified names (server.database.schema.object).

Step 4: Apply SQL Server Cumulative Update (If Applicable)

• Verify you are running the latest Cumulative Update (CU) for SQL Server 2025, as naming-validation bugs are occasionally patched.
• Check current version with:

  SELECT @@VERSION;

• Download and apply updates via Microsoft Update Catalog or SQL Server Installation Center on Windows Server 2025.

Alternative or Verification Steps

• Re-run the original query or job after shortening the identifier and confirm Error 9633 no longer appears in the SQL Server error log or SSMS output.
• If the error persists despite short names, check for hidden Unicode or whitespace characters in the identifier using SELECT DATALENGTH('YourName') to compare byte length versus character length.

Troubleshooting Guide: Service Broker Error 9634

Why This Error Happens

Error 9634 occurs when Service Broker fails to allocate sufficient memory for cryptographic operations, typically because SQL Server is under severe memory pressure from the operating system or other processes. This error is often a secondary symptom caused by an underlying issue such as insufficient system RAM, aggressive memory consumption by other applications, or incorrectly configured SQL Server memory limits. In some cases, corrupted or missing cryptographic provider files (such as bcrypt.dll or related CNG libraries) on Windows Server 2025 / Windows 11 can also prevent successful memory allocation for encryption routines.

Step-by-Step Resolution Guide

Step 1: Check SQL Server Error Log for Root Cause Messages

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for preceding messages (e.g., Error 701, 802, or 17803) that indicate low memory conditions or allocation failures.
• Alternatively, review the raw log file located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Review and Adjust SQL Server Memory Configuration

• Open SSMS, right-click the server instance, and select Properties > Memory.
• Verify that Maximum Server Memory (MB) is not set too restrictively (leave at least 20-25% of total RAM for the OS).
• Run the following T-SQL command to check current settings:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory';

• Adjust if necessary using sp_configure 'max server memory', <value_in_MB>; followed by RECONFIGURE;.

Step 3: Verify Windows System Memory and Resource Availability

• Open Task Manager or Performance Monitor (perfmon.exe) on Windows Server 2025 / Windows 11.
• Check the Available MBytes counter under Memory to confirm the OS isn’t starved for resources.
• Close or reconfigure competing memory-intensive applications/services running alongside SQL Server.

Step 4: Validate Cryptographic Provider Integrity

• Run the System File Checker to repair corrupted system DLLs tied to cryptographic functions:

  sfc /scannow

• Confirm that bcrypt.dll and ncrypt.dll exist and are not flagged as corrupted in C:\Windows\System32\.
• Restart the SQL Server service after the scan completes.

Step 5: Restart SQL Server Service and Monitor

• Open Services.msc, locate SQL Server (MSSQLSERVER), and restart the service.
• Re-enable Service Broker on the affected database if it was previously disabled due to the error:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

Alternative or Verification Steps

• Verify the fix by querying sys.dm_broker_queue_monitors and sys.dm_os_memory_clerks to confirm Service Broker memory allocation is stable and no further 9634 errors appear in the error log.
• If the issue persists, consider increasing the server’s physical RAM or migrating to a dedicated instance with lower contention, as this error frequently recurs under chronic OS-level memory pressure rather than a SQL-specific misconfiguration.

Troubleshooting Guide: Error 9632 – “The Salt Buffer Size Is Too Small”

Why This Error Happens

This error occurs when SQL Server’s encryption functions (such as those used in ENCRYPTBYPASSPHRASE, ENCRYPTBYKEY, or Always Encrypted operations) receive a salt value that doesn’t meet the minimum byte-length requirement for the specified algorithm. It is frequently triggered after applying cumulative updates to SQL Server 2025, where stricter cryptographic validation was introduced for FIPS-compliance enforcement. Additionally, corrupted or truncated certificate/key metadata in the master database can cause the salt generation routine to pass an undersized buffer during the encryption handshake.

Step-by-Step Resolution Guide

Step 1: Verify the SQL Server Build and Cryptographic Provider

• Run SELECT @@VERSION; to confirm you are on the latest SQL Server 2025 Cumulative Update (CU2 or later resolves known salt-buffer regressions).
• Check if FIPS mode is enforced via Local Security Policy: secpol.msc → Local Policies → Security Options → “System cryptography: Use FIPS compliant algorithms.”
• If FIPS is enabled, temporarily disable it in a test environment to confirm it is the root cause.

Step 2: Rebuild the Affected Encryption Keys

• Identify the affected key or certificate using:

SELECT * FROM sys.symmetric_keys;
SELECT * FROM sys.certificates;

• Drop and recreate the symmetric key with an explicit, supported algorithm (AES_256 is recommended over legacy RC4/DES variants):

DROP SYMMETRIC KEY YourKeyName;
CREATE SYMMETRIC KEY YourKeyName 
WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE YourCertName;

• Re-run the encryption function that previously failed (e.g., ENCRYPTBYKEY) to confirm the salt buffer is now correctly sized.

Step 3: Patch or Reinstall the Cryptographic API Components

• On Windows Server 2025 / Windows 11, ensure the OS is fully patched via Settings > Windows Update, as the underlying issue sometimes originates in the bcrypt.dll / ncrypt.dll libraries used by SQL Server’s CryptoAPI layer.
• Run sfc /scannow from an elevated Command Prompt to repair any corrupted system cryptographic DLLs.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) to reload the updated libraries.

Step 4: Adjust the Salt Length Parameter in Application Code

• If the error originates from a custom application or stored procedure explicitly passing a salt value, locate the function call and ensure the salt parameter meets the minimum 16-byte (128-bit) requirement for AES-based operations.
• Update the code:

DECLARE @salt VARBINARY(16) = CRYPT_GEN_RANDOM(16);

• Redeploy the stored procedure and test the encryption/decryption cycle end-to-end.

Alternative or Verification Steps

• Verify the fix by executing the previously failing query/procedure and confirming no Error 9632 appears in the SQL Server Error Log (EXEC xp_readerrorlog 0, 1;).
• If the issue persists, restore the master database from a pre-upgrade backup and reapply only the validated Cumulative Update, as corrupted key metadata may require a clean key regeneration from a healthy backup state.

Troubleshooting Guide: SQL Server Error 9630 — “The Signature Did Not Verify the Internal Hash”

Why This Error Happens

This error occurs when SQL Server 2025 detects that a signed module, certificate, or database object (such as a stored procedure, assembly, or backup signature) has an internal hash mismatch, indicating the signed content has been altered, corrupted, or tampered with after signing. This is commonly triggered by incomplete disk writes, antivirus interference during file operations, improper migration of signed objects between servers, or corruption in the master database’s metadata tables that store signature information. It can also occur if the certificate or asymmetric key used to validate the signature has been modified, re-issued, or is out of sync with the original signing key.

Step-by-Step Resolution Guide

Step 1: Verify the Integrity of the Affected Database and Objects

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying page-level corruption.
• Check the SQL Server error log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact object name tied to Error 9630.
• Identify whether the issue is tied to a specific signed stored procedure, CLR assembly, or backup set.

Step 2: Re-Validate or Re-Sign the Certificate/Key Pair

• Query sys.certificates and sys.crypt_properties to confirm the certificate thumbprint matches what was originally used to sign the object:

  SELECT * FROM sys.crypt_properties;
  SELECT * FROM sys.certificates;

• If the certificate appears altered or missing, drop the existing signature using DROP SIGNATURE and re-apply it with ADD SIGNATURE TO <object_name> BY CERTIFICATE <cert_name> WITH PASSWORD = '<StrongPassword>';
• Ensure the certificate private key file (.pvk) and public key (.cer) used for signing have not been modified since original deployment.

Step 3: Restore from a Known-Good Backup or Re-Deploy the Signed Module

• If re-signing fails, restore the affected database or object from the last verified clean backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE;
• For CLR assemblies, re-deploy the original .dll from source using CREATE ASSEMBLY with the WITH PERMISSION_SET = SAFE clause, then re-sign it.
• Confirm no third-party antivirus or Windows Defender real-time scanning is quarantining or modifying .mdf, .ldf, or assembly files in the SQL Server data directory (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).

Step 4: Check Windows Server 2025 / Windows 11 Security Policies

• Open Local Security Policy (secpol.msc) and verify that System cryptography: Use FIPS compliant algorithms is not misconfigured, as this can interfere with SQL Server’s internal signature validation.
• Confirm the SQL Server service account has full read/write access to the certificate store (certlm.msc under Local Computer > Personal).

Alternative or Verification Steps

• After applying fixes, run EXEC sys.sp_verify_database_ownership; or re-execute the originally failing operation (e.g., restore, procedure execution) to confirm Error 9630 no longer appears in the error log.
• If the issue persists, use sqlcmd -S localhost -Q "DBCC CHECKDB" from an elevated PowerShell terminal to isolate whether the corruption is OS-level (NTFS) rather than SQL Server-specific, and run chkdsk C: /f /r on the next reboot cycle.

Troubleshooting Guide: SQL Server Error 9631 — “The salt size is unacceptable for this key object”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to use an encryption key (such as a Database Encryption Key, Certificate, or Symmetric Key) whose salt value does not conform to the length requirements expected by the updated cryptographic provider in this release. This is most commonly caused by keys migrated from older SQL Server versions (such as 2016 or 2019) that used legacy salt generation algorithms incompatible with the stricter FIPS-compliant validation introduced in SQL Server 2025. It can also occur if the key metadata became corrupted during a backup/restore operation or if a third-party Extensible Key Management (EKM) module is returning a malformed salt buffer.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Key Object

• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to identify the specific database and key name referenced alongside Error 9631.
• Run the following query to cross-reference encryption metadata: SELECT * FROM sys.dm_database_encryption_keys;
• Note the encryption_state and key_algorithm columns for the flagged database.

Step 2: Regenerate the Encryption Key

• Drop the legacy key using DROP CERTIFICATE [YourCertName]; or DROP SYMMETRIC KEY [YourKeyName]; depending on the object type flagged in Step 1.
• Re-create the key using the modern SQL Server 2025 syntax to force a compliant salt length: CREATE CERTIFICATE [YourCertName] WITH SUBJECT = 'New Cert', START_DATE = '2025-01-01';
• If using Transparent Data Encryption (TDE), re-encrypt the Database Encryption Key (DEK): ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE [YourNewCertName];

Step 3: Update the Cryptographic Provider (If using EKM)

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn and verify the EKM provider DLL version matches the certified list for SQL Server 2025.
• If outdated, download the updated provider from your HSM/EKM vendor and reinstall using an elevated PowerShell session.
• Restart the SQL Server (MSSQLSERVER) service via services.msc or by running Restart-Service MSSQLSERVER -Force.

Step 4: Apply the Latest Cumulative Update

• Open Windows Settings > Windows Update on Windows Server 2025 to ensure the OS cryptographic libraries (CNG) are current.
• Download and install the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft Update Catalog to patch known salt-validation bugs in the Database Engine.
• Reboot the server to ensure the bcryptprimitives.dll and ncrypt.dll libraries are fully refreshed.

Alternative or Verification Steps

• Verify the fix by running SELECT encryptor_type, key_length FROM sys.certificates WHERE name = 'YourCertName'; and confirm the database no longer throws Error 9631 upon the next backup or encryption scan cycle.
• If the error persists, back up the database, restore it using the WITH REPLACE option into a sandbox instance, and attempt to re-key the encryption objects there to isolate whether the corruption is specific to the certificate or the physical database file.

Why This Error Happens

SQL Server Error 9628 occurs when the internal encryption/decryption routines receive a data buffer whose length is not a multiple of 8 bytes, which is a strict requirement for block-cipher operations (such as AES) used by Transparent Data Encryption (TDE), Always Encrypted, or backup encryption. This typically results from a corrupted encryption key, a damaged database page, or a third-party backup/compression tool that truncates or modifies the data stream before SQL Server can process it. It can also appear after an improper in-place upgrade to SQL Server 2025 where cryptographic provider libraries were not fully re-registered on Windows Server 2025 or Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify Database and Backup Integrity

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out page-level corruption.
• If using encrypted backups, confirm the backup file size in bytes is divisible by 8 using PowerShell: (Get-Item "D:\Backups\file.bak").Length % 8
• Re-run the backup/restore operation without third-party compression tools enabled, as some agents fragment the encrypted stream.

Step 2: Validate the Encryption Key and Certificate Chain

• Open a query window and check certificate/key status: SELECT * FROM sys.certificates; and SELECT * FROM sys.symmetric_keys;
• Drop and recreate the Database Encryption Key (DEK) if TDE is involved:
  ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE YourCertName;
• Ensure the certificate was backed up with both .cer and .pvk files, and restore them if the original certificate is missing or mismatched.

Step 3: Re-register Cryptographic Providers on Windows

• On Windows Server 2025 / Windows 11, open an elevated Command Prompt and re-register the Cryptographic Next Generation (CNG) providers:
  certutil -importpfx YourCert.pfx
• Restart the Cryptographic Services Windows service via services.msc or net stop CryptSvc && net start CryptSvc.
• Confirm SQL Server service account has Read permissions on the certificate’s private key folder (typically C:\ProgramData\Microsoft\Crypto\Keys).

Step 4: Apply the Latest SQL Server 2025 Cumulative Update

• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as early 2025 builds had a known buffer-alignment bug in the encryption module.
• After installation, restart the SQL Server service and re-test the failing operation (backup, restore, or encrypted column query).

Alternative or Verification Steps

• Re-run the original failing command (backup, restore, or encrypted query) and confirm Error 9628 no longer appears in the SQL Server Error Log (EXEC sp_readerrorlog;).
• If the error persists, restore the database from a known-good, pre-corruption backup and reapply transaction logs, then rebuild the encryption keys from scratch using CREATE DATABASE ENCRYPTION KEY.

Troubleshooting Guide: SQL Server Error 9629 – “Decrypted Signature Size Is Wrong”

Why This Error Happens

Error 9629 occurs when SQL Server attempts to validate a cryptographic signature (used in module signing, certificate-based authentication, or encrypted backups) but the decrypted output does not match the expected byte length. This typically happens due to a corrupted certificate or asymmetric key, a mismatch between the algorithm used to sign the object and the algorithm SQL Server expects during verification, or incomplete replication of key metadata after a certificate was recreated or restored. It can also surface after an in-place upgrade to SQL Server 2025 if legacy certificates were generated using deprecated cryptographic providers no longer fully compatible with the updated Crypto API (CAPI) libraries in Windows Server 2025/Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate or Key Integrity

• Run the following query to inspect the certificate associated with the signing operation:
• SELECT name, pvt_key_encryption_type_desc, algorithm_desc FROM sys.certificates WHERE name = 'YourCertName';
• Check the algorithm_desc column — SQL Server 2025 expects SHA2_256 or stronger; legacy SHA1-based certificates will frequently trigger Error 9629.
• If the certificate shows NULL or inconsistent metadata, it is likely corrupted and must be reissued.

Step 2: Re-Create the Certificate or Key Using a Supported Algorithm

• Drop the problematic certificate/key only after backing it up:
• BACKUP CERTIFICATE YourCertName TO FILE = 'C:\Backup\YourCertName.cer' WITH PRIVATE KEY (FILE = 'C:\Backup\YourCertName.pvk', ENCRYPTION BY PASSWORD = 'StrongPassword123!');
• Re-create it using an explicitly supported algorithm:
• CREATE CERTIFICATE YourCertName WITH SUBJECT = 'Signature Validation Cert', START_DATE = '2025-01-01';
• Re-sign the affected module (stored procedure, trigger, or assembly) using ADD SIGNATURE TO <object_name> BY CERTIFICATE YourCertName;

Step 3: Validate the Windows Cryptographic Provider Configuration

• Open certlm.msc (Local Machine Certificate Store) and confirm the certificate chain is trusted and not expired.
• Ensure the Windows Server 2025 / Windows 11 machine has the latest Cumulative Update installed, as CAPI/CNG provider updates can affect decryption output length.
• Restart the SQL Server (MSSQLSERVER) service after any certificate store changes:
• net stop MSSQLSERVER && net start MSSQLSERVER

Step 4: Check for TDE or Backup Encryption Mismatches

• If the error occurs during a restore operation, confirm the encryptor (certificate or asymmetric key) used for Transparent Data Encryption (TDE) is present on the destination server.
• Run RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourDB.bak'; to confirm the KeyAlgorithm and EncryptorThumbprint match an existing certificate on the target instance.

Alternative or Verification Steps

• After re-signing or re-creating the certificate, test the signed module by executing it directly and confirming no errors are thrown; also check sys.crypt_properties to ensure the signature entry now matches the expected byte length.
• If the issue persists, restore the original certificate from a known-good backup (CREATE CERTIFICATE ... FROM FILE ...) on a test instance to confirm whether the corruption originated at the database level or the Windows certificate store level.

Troubleshooting Guide: SQL Server Error 9627 — Hash Buffer Size Initialization Failure

Why This Error Happens

Error 9627 typically occurs when SQL Server’s cryptographic or hashing subsystem receives a buffer size that doesn’t match the expected algorithm output length, often due to a mismatch between the hash algorithm specified (e.g., SHA2256 vs SHA2512) and the buffer allocated by the calling application or extension. This can also stem from corrupted system CLR assemblies, outdated cryptographic providers, or incompatible third-party extensions/drivers interacting with HASHBYTES() or Always Encrypted column encryption functions. In some cases, a botched SQL Server 2025 cumulative update or Windows Server 2025 security patch misaligns the cryptographic API (CNG) libraries used internally by the Database Engine.

Step-by-Step Resolution Guide

Step 1: Verify the Hash Algorithm and Buffer Consistency

• Check any HASHBYTES() calls in stored procedures, functions, or application code for mismatched algorithm parameters.
• Confirm the declared variable size matches the algorithm output: SHA2256 = 32 bytes, SHA2512 = 64 bytes.
• Run the following to inspect usage across your database:

SELECT OBJECT_NAME(object_id), definition 
FROM sys.sql_modules 
WHERE definition LIKE '%HASHBYTES%';

Step 2: Repair or Update CNG Cryptographic Providers

• Open Windows PowerShell (Admin) and verify the status of the Cryptographic Services:

Get-Service CryptSvc | Select-Object Status, StartType

• Restart the service if it’s in a degraded state:

Restart-Service CryptSvc -Force

• Ensure Windows Server 2025 / Windows 11 has the latest cumulative update installed via Settings > Windows Update, since CNG library fixes are often bundled there.

Step 3: Validate SQL Server Cumulative Update Integrity

• Open SQL Server Configuration Manager and confirm the Database Engine service is running the latest SQL Server 2025 CU build.
• Re-run the SQL Server setup repair utility if corruption is suspected:

Setup.exe /ACTION=Repair /INSTANCENAME=MSSQLSERVER

• Check ERRORLOG (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related stack traces pointing to a specific DLL.

Step 4: Rebuild Affected Always Encrypted Column Keys (If Applicable)

• If the error ties to Always Encrypted operations, regenerate the Column Master Key (CMK) and Column Encryption Key (CEK) using SQL Server Management Studio (SSMS 21+).
• Navigate to Database > Security > Always Encrypted Keys, right-click, and select Rotate Keys to force a clean cryptographic handshake.

Alternative or Verification Steps

• Re-run the original query or application transaction that triggered Error 9627 — if it completes without exception, the fix is confirmed.
• As an alternative, isolate the issue by testing HASHBYTES() in a fresh tempdb query window to rule out database-specific corruption versus a server-wide cryptographic library fault.