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

by

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.