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

by

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.