Table Partition Merge Operation Failed - 3 Fixes

You're running ALTER PARTITION FUNCTION to merge two partitions and SQL Server throws Msg 4982: “ALTER PARTITION FUNCTION statement failed because one or more rows are not aligned with the new partition boundary.”

I've seen this exact error in SQL Server 2016, 2017, and 2019. The culprit is almost always one of three things. Let's go through them in order of likelihood.

1. Data straddles the boundary you're trying to merge

This is the big one. About 70% of the time, this error means there's data in the partition you're merging that doesn't belong there according to the new boundary range.

Say you have a partition function on a date column with boundaries at '2023-01-01' and '2023-04-01'. Partition 2 holds data from '2023-01-01' to '2023-03-31'. If you try to merge the boundary '2023-01-01' (which collapses partitions 1 and 2), SQL Server checks: does any row in partition 2 have a value less than '2023-01-01'? No—that data should be in partition 1. But if any row in partition 1 has a value >= '2023-01-01', the merge fails. The data isn't aligned with the new single partition's range.

The fix: Move those misaligned rows to the correct partition manually, then retry the merge.

-- Identify rows that are out of range
SELECT * 
FROM YourTable 
WHERE $PARTITION.PartitionFunctionName(PartitionColumn) = 1
  AND PartitionColumn >= '2023-01-01';

-- Move them with a SWITCH or UPDATE
-- Simplest: update the partition column value if possible
UPDATE YourTable 
SET PartitionColumn = '2022-12-31'
WHERE $PARTITION.PartitionFunctionName(PartitionColumn) = 1
  AND PartitionColumn >= '2023-01-01';

-- Then retry the merge
ALTER PARTITION FUNCTION PartitionFunctionName()
MERGE RANGE ('2023-01-01');

Don't bother checking partition alignment with sys.dm_db_partition_stats—it won't show you individual row values. You need to query the actual table using $PARTITION.

2. Non-aligned indexes on the table

This one's sneaky. You might have a clustered index that's partitioned on the same scheme as the table, but a nonclustered index that isn't. SQL Server requires all indexes on a partitioned table to use the same partition scheme and alignment. If you add a nonclustered index with a different filegroup or partition scheme, the merge will fail.

Check with this query:

SELECT 
    i.name AS IndexName,
    i.type_desc,
    p.partition_number,
    ds.name AS DataSpaceName
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('YourTable');

If you see a nonclustered index using a different partition scheme or no scheme at all, that's your problem.

The fix: Rebuild that nonclustered index on the same partition scheme as the table.

ALTER INDEX [YourNonClusteredIndex] ON YourTable
REBUILD WITH (ONLINE = ON)
ON PartitionSchemeName(PartitionColumn);

You can also drop and recreate it, but rebuild is faster and keeps metadata intact. After that, the merge should go through.

3. Partition function uses RANGE RIGHT and you're merging the wrong boundary

This trips up people moving from SQL Server 2008 or earlier. If your partition function is defined as RANGE RIGHT, the boundary value belongs to the right partition. Merging a boundary in a RANGE RIGHT scheme requires that the rightmost partition of the two being merged is empty—or at least has no rows that belong to the left partition.

Let's be concrete. RANGE RIGHT with boundaries ('2023-01-01', '2023-04-01') means:

• Partition 1: < '2023-01-01'
• Partition 2: >= '2023-01-01' AND < '2023-04-01'
• Partition 3: >= '2023-04-01'

If you merge boundary '2023-04-01' (merging partitions 2 and 3), partition 3 must have no rows. But if you accidentally try to merge '2023-01-01', you're collapsing partitions 1 and 2. In RANGE RIGHT, partition 1 must be empty for that merge—otherwise you get Msg 4982.

The fix: Check which boundary you're actually merging. Use this query to see the current partition ranges:

SELECT 
    pf.name AS PartitionFunction,
    boundary_id,
    value,
    CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' ELSE 'LEFT' END AS RangeType
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
ORDER BY boundary_id;

If you confirm it's RANGE RIGHT and you still want to merge, you must empty the left partition first. Move its data to another table or delete it, merge, then reload.

-- Move data from partition 1 to staging table
SELECT * INTO StagingTable
FROM YourTable
WHERE $PARTITION.PartitionFunctionName(PartitionColumn) = 1;

-- Truncate partition 1
TRUNCATE TABLE YourTable WITH (PARTITIONS(1));

-- Merge
ALTER PARTITION FUNCTION PartitionFunctionName()
MERGE RANGE ('2023-01-01');

-- Reload
INSERT INTO YourTable
SELECT * FROM StagingTable;

Yes, that's a lot of I/O. But it's the only reliable way for RANGE RIGHT merges when data exists.

Quick-reference summary

Start with cause #1. It's the most common and the fix is quick. If it's not that, then check indexes. If it's still failing, you're probably dealing with RANGE RIGHT logic. Don't overthink this—the error message tells you exactly what's wrong: rows aren't aligned. You just need to find which ones.