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

by

Troubleshooting Guide: SQL Server Error 4973 — ALTER TABLE SWITCH Partition Range Mismatch

Why This Error Happens

Error 4973 occurs when the boundary values of the source partition do not exactly match (are not a subset of) the boundary values of the target partition during a partitioned table switch operation. This typically happens when the partition functions or partition schemes for the two tables were created with different boundary ranges, data types, or precision/scale settings. It can also occur if a partition was split or merged after the initial table design, causing a drift between the source and target table’s partition definitions.

Step-by-Step Resolution Guide

Step 1: Verify Partition Function Boundaries on Both Tables

  • Run the following query to compare boundary values for both the source and target tables:
SELECT ps.name AS PartitionScheme, pf.name AS PartitionFunction, 
       prv.boundary_id, prv.value
FROM sys.partition_functions pf
JOIN sys.partition_schemes ps ON pf.function_id = ps.function_id
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
ORDER BY prv.boundary_id;
  • Confirm that the specific partition number mentioned in the error (e.g., partition %d) has identical LEFT/RIGHT range boundaries on both tables.
  • Check for mismatched data types (e.g., INT vs BIGINT) or differing precision in DATETIME2 columns, as these cause silent boundary mismatches.

Step 2: Check for Partition Function Drift from Split/Merge Operations

  • Query sys.partition_range_values on both the source and target partition functions to ensure the total count of boundaries matches.
  • If a SPLIT or MERGE operation was run on only one of the two partition functions, re-align them manually using:
ALTER PARTITION FUNCTION [YourPartitionFunction]()
SPLIT RANGE (N'BoundaryValueToMatch');
  • Ensure the PARTITION SCHEME used by both tables maps to the exact same filegroup sequence, not just the same function.

Step 3: Rebuild the Target Table with Matching Partition Definition

  • If boundaries cannot be reconciled via SPLIT/MERGE, drop and recreate the staging/target table using the exact partition function and scheme as the source table:
CREATE TABLE dbo.StagingTable (...)
ON [PartitionScheme_Matching] (PartitionColumn);
  • Copy existing data into the newly structured staging table before retrying the ALTER TABLE ... SWITCH command.
  • Confirm column collation, nullability, and constraints match exactly, as these are also validated during a switch operation alongside partition ranges.

Step 4: Re-run the ALTER TABLE SWITCH Operation

  • Execute the switch statement again specifying the exact partition number:
ALTER TABLE SourceTable SWITCH PARTITION 5 TO TargetTable PARTITION 5;
  • Monitor the SQL Server Error Log and Windows Event Viewer (Application log) for Event ID 4973 recurrence.

Alternative or Verification Steps

  • Verify success by querying sys.partitions on both tables post-switch to confirm rows count has moved from source to target partition as expected.
  • If the error persists, use $PARTITION.YourPartitionFunction(value) against known boundary test values on both tables to pinpoint exactly which partition number has a mismatched range before attempting the switch again.