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

by

Why This Error Happens

Error 4905 occurs because the ALTER TABLE ... SWITCH operation requires the target table (or target partition) to contain zero rows before the switch can take place. SQL Server enforces this because partition switching is a metadata-only operation, and merging data into a non-empty table/partition would violate the schema-level guarantee that no data loss or silent overwrite occurs. This typically happens when a staging table was reused from a previous load, a partition already contains leftover data from a prior failed job, or the wrong partition number was targeted.

Step-by-Step Resolution Guide

Step 1: Identify the Target Table or Partition State

  • Run SELECT COUNT(*) FROM [TargetTable]; to confirm whether the table itself has existing rows.
  • If using partitioned switching, query sys.partitions to check row counts per partition:
    SELECT partition_number, rows FROM sys.partitions WHERE object_id = OBJECT_ID('TargetTable');
  • Confirm which partition number is actually being targeted in your ALTER TABLE ... SWITCH PARTITION x TO ... PARTITION y statement.

Step 2: Clear or Truncate the Target Table/Partition

  • If it’s a whole staging table, run TRUNCATE TABLE [TargetTable]; to clear it instantly (metadata operation, minimal logging).
  • If it’s a specific partition, you cannot truncate a single partition directly in older behavior, but SQL Server 2025 supports TRUNCATE TABLE [TargetTable] WITH (PARTITIONS (y)); — use this to clear only the target partition.
  • Re-verify row count is zero using the same query from Step 1 before retrying the switch.

Step 3: Validate Schema and Constraint Alignment

  • Confirm the source and target tables have identical column definitions, data types, and collation settings — mismatches can sometimes mask as this error in SSMS logs.
  • Check that any check constraints or filtered indexes referenced for partition alignment match between source and target tables.
  • Ensure both tables reside in the same filegroup as required by the partition function if applicable.

Step 4: Re-run the ALTER TABLE SWITCH Statement

  • Execute the switch command again now that the target table/partition is confirmed empty:
    ALTER TABLE [SourceTable] SWITCH TO [TargetTable];
  • Monitor the SSMS “Messages” tab for confirmation that the command completed successfully with no additional errors (e.g., 4944 or 4972 related to constraint mismatches).

Alternative or Verification Steps

  • After the switch succeeds, run SELECT COUNT(*) FROM [TargetTable]; again to confirm the row count now reflects the migrated data from the source table.
  • If the issue persists despite an empty table, check for active transactions or open snapshots holding a lock on the partition using sys.dm_tran_locks, and resolve those sessions before retrying.