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

by

Troubleshooting Guide: SQL Server Error 4907 — Partition Count Mismatch

Why This Error Happens

Error 4907 occurs when a partitioned table and its associated index (typically a non-aligned index) are built against different partition schemes, resulting in a mismatched number of partitions. This commonly happens after a SPLIT, MERGE, or ALTER PARTITION SCHEME operation is applied to the table’s partition function without correspondingly updating all indexes, including non-clustered indexes that may be partitioned independently. It can also surface during SWITCH PARTITION operations or online index rebuilds (new in SQL Server 2025’s enhanced resumable indexing) where metadata for the table and index partitions falls out of sync due to a partially completed DDL transaction or corrupted system metadata.

Step-by-Step Resolution Guide

Step 1: Identify the Mismatched Objects

  • Run the following query to find the table and index names referenced in the error message:
SELECT t.name AS TableName, i.name AS IndexName, 
       ps.name AS PartitionScheme, i.index_id
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE t.name = 'YourTableName';
  • Note the %.*ls values from the original error (table name and index name) to confirm which object pair is out of sync.
  • Check partition counts explicitly:
SELECT OBJECT_NAME(object_id) AS ObjName, index_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('YourTableName')
ORDER BY index_id, partition_number;

Step 2: Rebuild the Index on the Correct Partition Scheme

  • If the index is non-aligned (using a different scheme than the base table), rebuild it explicitly on the table’s partition scheme:
ALTER INDEX [YourIndexName] ON [dbo].[YourTableName] 
REBUILD PARTITION = ALL 
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
  • If the index was intentionally non-aligned, instead re-create it using CREATE INDEX ... WITH (DROP_EXISTING = ON) and explicitly match the partition scheme:
CREATE INDEX [YourIndexName] ON [dbo].[YourTableName](ColumnName)
ON [YourPartitionScheme](PartitionColumn)
WITH (DROP_EXISTING = ON, ONLINE = ON);

Step 3: Verify Partition Function and Scheme Alignment

  • Confirm the partition function boundaries used by the table and index match exactly:
SELECT pf.name AS FunctionName, pf.fanout, ps.name AS SchemeName
FROM sys.partition_functions pf
JOIN sys.partition_schemes ps ON pf.function_id = ps.function_id;
  • If a recent SPLIT or MERGE was applied, re-run ALTER PARTITION SCHEME on any index-specific schemes that were missed during the original operation.

Step 4: Check for Incomplete DDL or Corrupted Metadata

  • Run DBCC CHECKTABLE('YourTableName') WITH DATA_PURITY to rule out metadata corruption.
  • If corruption is suspected, restore the table from the last known-good backup rather than attempting repair, since partition metadata repairs are not supported by DBCC CHECKDB repair options.
  • On Windows Server 2025, verify no antivirus or Windows Defender real-time scanning interfered with the .mdf/.ndf files during the DDL operation; add SQL Server data paths to exclusions (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).

Step 5: Re-run the Original Failing Statement

  • Once indexes and schemes are aligned, re-execute the original ALTER TABLE ... SWITCH PARTITION or index maintenance statement that triggered Error 4907.
  • Monitor SQL Server Error Log and Windows Event Viewer (Application log) for confirmation that the operation completes without further 4907 errors.

Alternative or Verification Steps

  • Verify the fix by querying sys.partitions again and confirming the index and table report identical partition counts under the partition_number column.
  • If the error persists after rebuilding, drop and fully recreate the non-aligned index from scratch using CREATE INDEX instead of DROP_EXISTING, as this forces SQL Server to re-evaluate the partition scheme assignment from a clean state.