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

by

Troubleshooting Guide: SQL Server Error 8997 (Service Broker Msg)

Why This Error Happens

Error 8997 typically occurs when Service Broker detects data corruption or an integrity violation while validating message bodies, conversation handles, or dialog metadata within the sys.transmission_queue or related system tables. This is often triggered by abrupt service termination, storage-level corruption (disk I/O errors), or inconsistent database states caused by improper failover or backup restoration. In some cases, it stems from mismatched encryption keys or certificates used for secure conversations between Service Broker endpoints.

Step-by-Step Resolution Guide

Step 1: Run Database Consistency Checks

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance on Windows Server 2025.
  • Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify corruption in Service Broker objects.
  • Review the output for errors related to sys.conversation_endpoints or sys.transmission_queue.

Step 2: Validate Service Broker Configuration

  • Confirm Service Broker is enabled by running SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';.
  • If disabled or in a bad state, run ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;.
  • Check certificate and key validity used for secure dialogs under Security > Certificates in the database node within SSMS.

Step 3: Clear Corrupted Transmission Queue Entries

  • Query the queue using SELECT * FROM sys.transmission_queue; to locate stalled or malformed messages.
  • End problematic conversations manually with END CONVERSATION 'ConversationHandle' WITH CLEANUP;.
  • Restart the SQL Server service via services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER'.

Step 4: Repair or Restore the Database

  • If corruption persists, run DBCC CHECKDB ('YourDatabaseName') WITH REPAIR_ALLOW_DATA_LOSS; only as a last resort in a maintenance window.
  • Alternatively, restore the database from the last known good backup using the SSMS restore wizard or RESTORE DATABASE T-SQL command.
  • Re-enable Service Broker post-restoration and verify GUID consistency with sys.service_broker_guid.

Alternative or Verification Steps

  • Verify the fix by monitoring the SQL Server Error Log (Management > SQL Server Logs in SSMS) for recurrence of Error 8997 over a 24-hour period.
  • If the issue persists, consider rebuilding the Service Broker GUID using ALTER DATABASE YourDatabaseName SET NEW_BROKER; (note: this breaks existing conversations and should only be used if message continuity is not critical).