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

by

Troubleshooting Guide: SQL Server Error 4904 — ALTER TABLE SWITCH Partition Must Be Empty

Why This Error Happens

This error occurs because the target partition specified in the ALTER TABLE...SWITCH statement already contains data, and SQL Server requires the destination partition to be completely empty before a switch operation can take place. This typically happens when a previous load, SWITCH operation, or manual insert populated the partition, or when the partition boundary calculation points to the wrong partition number due to a misaligned partition function. It can also surface when staging tables aren’t properly truncated between ETL cycles.

Step-by-Step Resolution Guide

Step 1: Identify the Target Partition and Confirm Row Count

  • Run the following query to check row counts across all partitions of the target table:
  SELECT partition_number, rows 
  FROM sys.partitions 
  WHERE object_id = OBJECT_ID('YourTargetTable') 
  AND index_id IN (0,1);
  • Confirm whether the partition number reported in Error 4904 actually contains rows.
  • Cross-reference this with sys.partition_range_values to validate the boundary values align with your expected partition.

Step 2: Truncate or Clear the Target Partition

  • If the partition is unexpectedly populated, clear it using a partition-level truncate (SQL Server 2016+ syntax, fully supported in SQL Server 2025):
  TRUNCATE TABLE YourTargetTable WITH (PARTITIONS (3));
  • Replace 3 with the actual partition number reported in the error message.
  • Avoid using a full DELETE statement, as it is significantly slower and generates excessive transaction log activity compared to a targeted partition truncate.

Step 3: Validate the Partition Function and Scheme Alignment

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and run:
  SELECT * FROM sys.partition_functions;
  SELECT * FROM sys.partition_range_values;
  • Ensure the source table’s partition boundary matches exactly with the target table’s partition boundary — mismatched boundaries are a common root cause of rows unexpectedly landing in the wrong partition.
  • If using a sliding window ETL pattern, verify your partition maintenance script correctly splits/merges boundaries before attempting the switch.

Step 4: Re-run the ALTER TABLE SWITCH Statement

  • Execute the switch operation again, specifying the correct partition number explicitly:
  ALTER TABLE SourceTable SWITCH PARTITION 3 TO YourTargetTable PARTITION 3;
  • Monitor for Error 4904 recurrence; if it persists, check for open transactions or replication processes holding locks on the partition that might be re-inserting data.

Step 5: Check for Concurrent Processes Writing to the Partition

  • On Windows Server 2025, open Task Scheduler or your SQL Agent Job history to confirm no concurrent ETL job is writing to the target partition simultaneously.
  • Use sys.dm_tran_locks to identify any blocking sessions holding locks on the target table during the switch window.

Alternative or Verification Steps

  • Verify the fix by re-querying sys.partitions immediately after the switch to confirm the row count moved correctly from source to target partition and that the source partition is now empty.
  • As an alternative, if partition alignment issues persist, drop and recreate the partition scheme/function with corrected boundary values, then reload data via INSERT...SELECT instead of relying on SWITCH until the structure is verified stable.