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

by

Troubleshooting Guide: Error 6390 – ALTER SCHEMA COLLECTION Failed Due to Selective XML Index Path Invalidation

Why This Error Happens

This error occurs when you attempt to modify an XML Schema Collection that is actively referenced by a Selective XML Index (SXI) on a table, and the proposed schema change would invalidate the specific XML path currently being indexed. SQL Server 2025 performs a validation pass during ALTER SCHEMA COLLECTION operations, and if the new schema definition no longer matches the node type, data type, or structure expected at the indexed path, the engine blocks the change to prevent index corruption. This typically happens after a source system update changes an XML element’s data type (e.g., from xs:string to xs:integer) or removes/renames a node that a secondary XML index relies on.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Index and Path

  • Run the following query to find the exact selective XML index and promoted path causing the conflict:
SELECT si.name AS IndexName, sp.path_expression, t.name AS TableName
FROM sys.selective_xml_index_paths sp
JOIN sys.indexes si ON sp.index_id = si.index_id AND sp.object_id = si.object_id
JOIN sys.tables t ON sp.object_id = t.object_id;
  • Cross-reference the path_expression output with the %.*ls path noted in the Error 6390 message to confirm the exact node in conflict.

Step 2: Drop the Selective XML Index Temporarily

  • Before altering the schema collection, you must remove the dependency:
DROP INDEX [SXI_IndexName] ON [dbo].[YourTable];
  • Note: On large XML datasets (100GB+), ensure you have adequate tempdb space, as dropping an SXI with “secondary indexes” (VALUE, PATH, or PROPERTY) can trigger significant tempdb usage during the subsequent rebuild.

Step 3: Apply the ALTER SCHEMA COLLECTION Command

  • With the index removed, the schema collection is now free of the dependency lock. Execute your intended change:
ALTER XML SCHEMA COLLECTION [dbo].[YourSchemaCollection]
ADD N'<xsd:schema>...</xsd:schema>';
  • Ensure the new schema definition aligns with the data types expected by your application layer to prevent future casting errors.

Step 4: Recreate the Selective XML Index

  • Rebuild the SXI using the CREATE SELECTIVE XML INDEX syntax, redefining the promoted paths to match the new schema structure.
  • If the path name changed (e.g., /Root/Customer/ID to /Root/Customer/CustID), update the PATH argument in the WITH XMLNAMESPACES or path definition clause accordingly to prevent the error from recurring on the next refresh.

Alternative or Verification Steps

  • Verification: After recreating the index, run DBCC CHECKCONSTRAINTS and query sys.xml_indexes to confirm the is_valid status is 1 and the index is no longer flagged as suspect.
  • Alternative: If dropping the production index is not feasible due to uptime requirements, create a new schema collection version, update the XML column’s validation reference to the new collection, and migrate the SXI during a scheduled maintenance window rather than performing an in-place ALTER.