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

by

Troubleshooting Guide: Error 11446 – XML_COMPRESSION Mismatch in ALTER TABLE SWITCH

Why This Error Happens

This error occurs because SQL Server 2025 requires the source table (or partition) and the target table (or partition) to have identical XML_COMPRESSION settings on all XML columns before a partition switch operation can be performed. When one table was created or altered with XML_COMPRESSION = ON and the other defaults to OFF (or vice versa), the schema metadata becomes incompatible for the SWITCH operation. This is a metadata-level enforcement check, not a data corruption issue, meaning SQL Server is blocking the switch to prevent inconsistent storage formats within the same partitioned table.

Step-by-Step Resolution Guide

Step 1: Identify the XML_COMPRESSION State on Both Tables

  • Run the following query against both the source and target tables to inspect current compression settings:
SELECT c.name AS ColumnName, xsc.xml_compression 
FROM sys.columns c 
JOIN sys.xml_schema_collections xsc ON c.xml_collection_id = xsc.xml_collection_id 
WHERE c.object_id = OBJECT_ID('YourSchema.YourTable');
  • Alternatively, check sys.partitions and sys.columns catalog views for xml_compression flags on the specific partition involved.
  • Confirm which table (source or target) has the mismatched setting.

Step 2: Align XML_COMPRESSION Settings Using ALTER TABLE

  • Apply the correct compression setting to the mismatched table/column using:
ALTER TABLE YourSchema.YourTable 
REBUILD PARTITION = ALL 
WITH (XML_COMPRESSION = ON);
  • If only a specific partition is out of sync, target that partition number explicitly:
ALTER TABLE YourSchema.YourTable 
REBUILD PARTITION = 5 
WITH (XML_COMPRESSION = ON);
  • Ensure you match the exact setting (ON or OFF) found on the opposite table in Step 1.

Step 3: Rebuild Indexes Impacted by the Compression Change

  • Since changing XML_COMPRESSION can invalidate existing XML indexes, rebuild them explicitly:
ALTER INDEX ALL ON YourSchema.YourTable REBUILD;
  • On Windows Server 2025, ensure the SQL Server service account has sufficient disk space in the data drive (check via Server Manager > Storage) since rebuilding large XML indexes can temporarily double storage usage.

Step 4: Retry the ALTER TABLE SWITCH Operation

  • Execute the original switch command again:
ALTER TABLE YourSchema.SourceTable 
SWITCH PARTITION 5 TO YourSchema.TargetTable PARTITION 5;
  • Confirm no other schema mismatches (such as data types or constraints) are flagged in the same operation.

Alternative or Verification Steps

  • Verify the fix by re-running the inspection query from Step 1 on both tables — the xml_compression values should now match exactly before attempting the switch again.
  • If the error persists, use SQL Server Management Studio (SSMS) 21’s “Generate Scripts” wizard to compare the full CREATE TABLE definitions side-by-side, ensuring no secondary XML column or schema collection was missed during remediation.