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

by

Troubleshooting Guide: SQL Server Error 6399 — Selective XML Index Path Dependency Conflict

Why This Error Happens

This error occurs because the selective XML index path you are attempting to drop is a shared dependency used by one or more secondary selective XML indexes defined on the same XML column. SQL Server enforces a strict dependency hierarchy where primary selective XML index paths cannot be removed while secondary indexes still reference them, in order to prevent orphaned or corrupted index structures. This typically happens during schema refactoring, index maintenance scripts, or automated deployment pipelines that attempt to drop paths out of order.

Step-by-Step Resolution Guide

Step 1: Identify All Dependent Secondary Indexes

  • Query sys.selective_xml_index_paths and sys.xml_indexes to find all secondary selective XML indexes associated with the same primary XML index.
  • Use the following query to list dependencies:
SELECT xi.name AS IndexName, sxip.path, xi.secondary_type_desc
FROM sys.xml_indexes xi
JOIN sys.selective_xml_index_paths sxip 
    ON xi.object_id = sxip.object_id AND xi.index_id = sxip.index_id
WHERE xi.object_id = OBJECT_ID('YourTableName');
  • Note the names of every secondary index referencing the path you intend to remove.

Step 2: Drop Secondary Indexes First

  • Drop each dependent secondary selective XML index before touching the primary path.
  • Use the standard DROP INDEX syntax for each identified index:
DROP INDEX SecondaryIndexName ON YourTableName;
  • Repeat for all secondary indexes listed in Step 1 to clear the dependency chain.

Step 3: Drop the XML Path from the Primary Index

  • Once no secondary indexes reference the path, drop the XML path using sp_xml_remove_selective_index_path or the appropriate ALTER INDEX statement.
  • Example:
EXEC sp_xml_remove_selective_index_path 
    @object_name = 'YourTableName', 
    @path = '/your/xml/path';
  • Confirm no errors are returned during execution.

Step 4: Rebuild or Recreate Secondary Indexes (If Needed)

  • If the secondary indexes are still required for query performance, recreate them using updated paths that do not reference the removed path.
  • Validate the new index definitions against your current XML schema collection.
  • Run DBCC CHECKDB or DBCC CHECKTABLE on the affected table to ensure structural integrity after index changes.

Alternative or Verification Steps

  • Re-run the query from Step 1 against sys.selective_xml_index_paths to confirm the path no longer appears and no orphaned secondary index references remain.
  • If the error persists, check for cached execution plans or open transactions holding schema locks on the table using sys.dm_tran_locks, and retry the drop operation after clearing active sessions.