Fix table partition merge failure in SQL Server

Quick answer

Run ALTER PARTITION FUNCTION with MERGE RANGE after verifying the boundary is empty — if it's not, switch the data out first, or use SWITCH to empty the partition.

Context

You're working with a partitioned table in SQL Server — maybe a big logging table or a sales table split by month. You try to merge two partitions, say merging January into February, and you get error 4902: "Cannot alter partition function because partition number X is not empty." Or error 5009: "The partition boundary value specified is not a valid input."

This happens because the partition you're trying to merge still has data, or there's a non-aligned index, or the merge conflicts with an ongoing transaction. I've seen this most often in SQL Server 2016-2019 when someone tries to shrink the number of partitions without checking that the partition being removed actually has zero rows. The engine won't let you merge a boundary that contains data — it's a safety measure to prevent data loss.

Another common trigger: you've deleted rows from a partition but didn't rebuild the indexes. Deletes leave behind ghost records that still count as "data" from the partition's perspective. The merge sees those as rows and blocks the operation.

Fix steps

1. Identify the partition with the boundary you're merging.
   Run this query to see which partition contains the boundary value you're targeting:
   

   SELECT $PARTITION.PartitionFunctionName(20220101) AS PartitionNumber;
   -- Replace PartitionFunctionName and the boundary value with yours

   
   After running, you'll see the partition number involved.
2. Check if the partition has any rows.
   Use:
   

   SELECT COUNT(*) FROM YourPartitionedTable
   WHERE $PARTITION.PartitionFunctionName(PartitionColumn) = PartitionNumber;
   -- Replace PartitionNumber from step 1

   
   If the count is zero, proceed to step 4. If it's greater than zero, go to step 3.
3. Move data out of the partition.
   You have two options:
   • Switch the data to a staging table: Create a table with the same schema, then run ALTER TABLE ... SWITCH PARTITION X TO StagingTable. This empties the partition instantly. Then merge. Later, you can either drop the staging table or reinsert the data into the appropriate partition.
   • Delete and rebuild: If switching feels heavy (it's not — it's metadata-only), you can delete rows, but you must rebuild the clustered index on that partition afterward. Deletes alone won't work because ghost cleanup is lazy.
     ALTER INDEX ALL ON YourPartitionedTable REBUILD PARTITION = PartitionNumber;
4. Merge the boundary.
   Now run:
   

   ALTER PARTITION FUNCTION PartitionFunctionName() MERGE RANGE ('20220101');
   -- Use the boundary value you want to remove

   
   After executing, you should see "Command(s) completed successfully." If you get error 5009, the boundary value doesn't exist in the function — check SELECT * FROM sys.partition_range_values to confirm.
5. Verify the merge worked.
   Query the partition function again:
   

   SELECT * FROM sys.partition_range_values WHERE function_id = OBJECT_ID('PartitionFunctionName');

   
   You should see one less row than before.

Alternative fixes

If the main fix doesn't work — maybe you keep getting timeout errors or locking conflicts — try one of these:

• Use online partition merge. In SQL Server 2019 and later, you can specify WITH (ONLINE = ON) on the ALTER INDEX ... REBUILD step. This reduces blocking but takes longer. Then try the merge again.
• Check for non-aligned indexes. Some indexes might not be partitioned on the same scheme. Run:
  

  SELECT i.name, i.type_desc, p.partition_number
  FROM sys.indexes i
  JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  WHERE i.object_id = OBJECT_ID('YourPartitionedTable')
  AND p.partition_number = PartitionNumber;

  
  If an index shows a different partition scheme, drop and recreate it aligned with the table's partition function.
• Kill blocking sessions. Sometimes a long-running query holds a lock on the partition. Use sp_who2 or sys.dm_exec_requests to find and stop the blocker.

Prevention tip

Don't let partitions sit empty for months — they still cause overhead in metadata and maintenance. Instead, merge partitions during your regular ETL window, right after you've moved data out. Set up a job that runs SWITCH to a staging table, then merges, then drops the staging table. This keeps your partition layout clean and prevents these errors from popping up mid-day when everyone's working.