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

by

Troubleshooting Guide: Error 4959 — ALTER TABLE SWITCH Partition TEXT Filegroup Mismatch

Why This Error Happens

This error occurs because SQL Server 2025 requires that corresponding partitions on both the source and target tables in an ALTER TABLE...SWITCH operation reside on the same filegroup for TEXT/NTEXT/IMAGE (large object) data. When the partitioned table’s TEXT filegroup for a specific partition does not match the target table’s TEXT filegroup for its corresponding partition, SQL Server blocks the switch to prevent orphaned LOB data or storage inconsistencies. This typically happens when tables were created with different partition schemes, or when a table was manually reconfigured without updating the LOB storage mapping to align with the source table.

Step-by-Step Resolution Guide

Step 1: Identify the Filegroup Mismatch

  • Run the following query to inspect filegroup assignments for both tables:
  SELECT t.name AS TableName, p.partition_number, fg.name AS FilegroupName
  FROM sys.tables t
  JOIN sys.partitions p ON t.object_id = p.object_id
  JOIN sys.allocation_units au ON p.partition_id = au.container_id
  JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
  WHERE t.name IN ('SourceTable', 'TargetTable')
  ORDER BY t.name, p.partition_number;
  • Note which partition numbers show differing FilegroupName values between the source and target table.

Step 2: Recreate the Target Table with Matching TEXT Filegroup

  • Drop or rename the existing target (or staging) table that has the mismatched LOB filegroup.
  • Recreate it using the TEXTIMAGE_ON clause pointing to the exact same filegroup as the source table’s corresponding partition:
  CREATE TABLE dbo.TargetTable (
      -- column definitions matching source table
  )
  ON PartitionScheme(PartitionColumn)
  TEXTIMAGE_ON PartitionScheme(PartitionColumn);
  • Ensure both tables use the same partition scheme and same filegroup mapping for LOB data, not just matching row data filegroups.

Step 3: Align Partition Schemes and Functions

  • Verify both tables share an identical partition function and partition scheme definition:
  SELECT * FROM sys.partition_functions;
  SELECT * FROM sys.partition_schemes;
  • If discrepancies exist, use CREATE PARTITION SCHEME to rebuild the target table’s scheme so that LOB filegroups align 1:1 with the source table across every partition boundary.

Step 4: Reload Data and Retry the Switch

  • Populate the corrected target table with the appropriate data (if rebuilt from scratch) using INSERT INTO...SELECT or BULK INSERT.
  • Re-run the original switch statement:
  ALTER TABLE dbo.SourceTable SWITCH PARTITION 5 TO dbo.TargetTable PARTITION 5;

Alternative or Verification Steps

  • Confirm the fix by re-running the sys.allocation_units/sys.filegroups query from Step 1 — all partition numbers should now show identical FilegroupName values for both tables.
  • If rebuilding the table is not feasible, as a workaround, create a new filegroup matching the exact name/structure used by the source table and use ALTER TABLE...REBUILD PARTITION with the correct TEXTIMAGE_ON filegroup before retrying the switch.