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

by

Troubleshooting Guide: SQL Server Error 4940 — ALTER TABLE SWITCH Filegroup Mismatch

Why This Error Happens

This error occurs because SQL Server’s partition SWITCH operation requires the source table, target table, and all their respective indexes to reside on the exact same filegroup. When a table or its indexes were created using the default filegroup while the partitioned table uses a named filegroup (or vice versa), SQL Server detects the physical storage mismatch and blocks the metadata-only switch operation. This is purely a schema/storage alignment issue, not a data corruption or permissions problem.

Step-by-Step Resolution Guide

Step 1: Identify the Filegroup Mismatch

  • Run the following query to check filegroup placement for both tables involved in the switch:
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    fg.name AS FilegroupName
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.data_spaces fg ON i.data_space_id = fg.data_space_id
WHERE t.name IN ('SourceTable', 'TargetTable');
  • Note any discrepancies between heap/clustered index placement and nonclustered index placement.

Step 2: Align the Target Table’s Filegroup

  • If the target (partitioned) table lives on a named filegroup (e.g., FG_Archive), recreate the source staging table on that same filegroup:
CREATE TABLE dbo.StagingTable (...) ON FG_Archive;
  • For existing tables, move the clustered index to the correct filegroup:
CREATE CLUSTERED INDEX CIX_Staging ON dbo.StagingTable (ColumnName) 
WITH (DROP_EXISTING = ON) ON FG_Archive;

Step 3: Align All Nonclustered Indexes

  • Nonclustered indexes must also match filegroups — this is the most commonly missed step.
  • Rebuild each nonclustered index explicitly onto the target filegroup:
CREATE NONCLUSTERED INDEX IX_Name ON dbo.StagingTable (ColumnName)
WITH (DROP_EXISTING = ON) ON FG_Archive;
  • If the table uses a partition scheme instead of a single filegroup, ensure the staging table uses the identical partition scheme and partition function boundary.

Step 4: Re-run the ALTER TABLE SWITCH

  • Execute the switch statement again:
ALTER TABLE dbo.StagingTable SWITCH TO dbo.PartitionedTable PARTITION 5;
  • Confirm no error 4940 is raised and the operation completes as a fast metadata-only change.

Alternative or Verification Steps

  • Verify success by querying sys.partitions to confirm row counts shifted from the staging table to the correct partition number without any data movement delay.
  • If rebuilding indexes on-the-fly is not feasible due to size, consider scripting out the staging table via SSMS “Script Table As” and explicitly specifying the ON [FilegroupName] clause for both the table and all index definitions before re-creating it.