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