SQL Server Merge Replication: Identity range check constraint doesn’t match identity range allocations

SQL Server Merge Replication is great when it works, but when it goes awry it can take a while to figure out what the problem is and then rectify it.

In this article, I look at the cause of a specific issue where one or more identity range check constraints do not match up with the identity range allocations for the associated articles.

I explain what the likely causes are and how to resolve this particular issue.

The problem

The problem I am covering in this article occurs when an identity range check constraint gets out of sync with the identity range allocations that are assigned to the merge replication article.

Note that although the term ‘article’ can refer to a table, view, stored procedure or user-defined function, I will use the term ‘table’ in most of the sections below for simplicity.

Identity ranges

In order to replicate data from multiple subscriber databases correctly, SQL Server Merge Replication must keep track of identity (ID) ranges.

Note that if you are using UNIQUEIDENTIFIER IDs for your database tables, then this isn’t an issue. This only concerns numeric IDs which have the potential to conflict with each other.

By managing the identity ranges which are allocated to each database, the replication sub-system can ensure that each new record that is added to a database table has an identity value that is unique and does not clash with an ID from another database.

Typically the ranges will be handed out sequentially e.g. database A gets ID ranges 1-1000 and 1001-2000 while database B gets ID ranges 2001-3000 and 3001-4000 etc. These range allocations are stored in a table in the ‘distribution’ database called ‘MSmerge_identity_range_allocations’ on a per article basis.

Each time a new set of ranges is assigned to a subscriber, the identity range check constraint within the associated article/table is updated to match up with the identity range allocations which are stored in the distribution database.

Here is an example of the identity range check constraint expression for a table.

([ID]>(2001) AND [ID]<=(3000) OR [ID]>(3001) AND [ID]<=(4000))

Needless to say, it is vital that the range allocations and the check constraint stay in sync.

Error details

Whenever the identity range allocations do not comply with the identity range check constraint, an error similar to the following will typically be raised whenever a client attempts to add new records to the affected table.

Msg 548, Level 16, State 2, Line 1
The insert failed. It conflicted with an identity range check constraint in database ‘<database_name>’, replicated table ‘dbo.<table_name>’, column ‘<column_name>’. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
The statement has been terminated.

The above error message suggests that you use the sp_adjustpublisheridentityrange stored procedure to fix the problem. However, in the scenario I am referring to this isn’t enough to address the root of the problem.

Unlike the somewhat common scenario where the publisher was simply unable to update the identity ranges for a table, the problem I am referring to occurs whenever the identity range check constraint has gotten out of sync (sometimes quite badly) with the distribution database and this typically affects multiple tables in the database.

As an example, one of the identity range check constraints may be enforcing a check for an ID range of 2001-3000 but the identity range allocation is 14001-15000.

Possible causes

The cause of this particular problem, like many other replication issues, can sometimes be a mystery. However, there are a number of possible reasons for it coming to light.

Whatever the exact reason is, it is usually a result of the publication database getting out of sync with the distribution database. For example, the publication database needed to be restored to an earlier version but there wasn’t a corresponding distribution database backup available to restore at the same time.

Either way, the problem causes major issues for users as the violation of the identity range check constraints will prevent the storage of any new records in the database for the affected tables.

Let’s now look at some ways to resolve this particular issue.

Individual intervention

If you are confident that the issue has only affected a few known tables, the best option may be to apply a fix on an individual basis.

To fix the issue for a single table, you can use the sp_MSrefresh_publisher_idrange stored procedure, as per the following example.

EXEC sys.sp_MSrefresh_publisher_idrange 
    '[dbo].[DimProduct]',                   -- qualified_object_name NVARCHAR(517) The Table Name.
    '00000000-0000-0000-0000-000000000000', -- subid UNIQUEIDENTIFIER The Subscriber ID.
    '00000000-0000-0000-0000-000000000000', -- artid UNIQUEIDENTIFIER The Article ID.
    2,                                      -- ranges_needed TINYINT The number of ranges to update.
    1                                       -- refresh_check_constraint BIT Whether or not to refresh the check constraint.

The sp_MSrefresh_publisher_idrange stored procedure can be used to refresh the identity range check constraint and update the identity range allocations for a specific article/table.

Note that before running the above SQL, you’ll need to update the arguments which are passed to the sp_MSrefresh_publisher_idrange stored procedure to suit your environment.

The first argument is the qualified_object_name which should be set to the full name of the table you need to update the ranges and check constraint for.

The second argument is the subid which should be set to the unique identifier of the subscriber. You can find the Subscriber ID by querying the ‘sysmergesubscriptions’ table within the distribution database.

The third argument is the artid which should be set to the unique identifier of the article/table. You can find the Article ID by querying the ‘sysmergearticles’ table within the distribution database.

The fourth argument is the number_of_ranges which is used to configure the number of ranges you want to update. You can pass 0 for none, 1 for one range or 2 for both ranges. I prefer to specify a value of 2 to update both ranges, in order to make sure that everything is fully refreshed.

The fifth and last argument is refresh_check_constraint which controls whether or not the check constraint for the specified article is updated. You should make sure to set this to 1, in order to update the check constraint to match the stored identity range allocations.

After running this SQL, inspect the check constraint for the article you specified and you should find that it has been updated to match the ranges specified in the ‘MSmerge_identity_range_allocations’ table within the distribution database.

A global solution

If the problem is more extensive and has affected many or most of the tables in your database, a more automated solution that applies a fix to all tables is going to be a better approach.

Below is a SQL solution that iterates through all of the merge articles/tables in your publication and updates the identity ranges and the identity range check constraint for each.

-- Set up the variables needed to refresh the identity ranges and identity range check constraint.
DECLARE @pubid			  AS UNIQUEIDENTIFIER;
DECLARE @qualified_object_name    AS NVARCHAR(517);
DECLARE @subid                    AS UNIQUEIDENTIFIER;
DECLARE @artid                    AS UNIQUEIDENTIFIER;
DECLARE @ranges_needed            AS TINYINT
DECLARE @refresh_check_constraint AS BIT
 
SET @pubid		      = '00000000-0000-0000-0000-000000000000'; -- CHANGE THIS TO PUBLICATION GUID.
SET @subid		      = '00000000-0000-0000-0000-000000000000'; -- CHANGE THIS TO SUBSCRIBER GUID.
SET @ranges_needed	      = 2;
SET @refresh_check_constraint = 1;
 
-- Create a temp table containing the list of article names, articles IDs and row numbers.
SELECT DISTINCT name, artid, ROW_NUMBER() OVER(ORDER BY name ASC) AS row 
INTO  #TempArticleListTable
FROM  sysmergearticles
WHERE pubid = @pubid;
 
-- Get the row count / number of articles in the publication from the temp table.
DECLARE @rowCount INT;
SET     @rowCount = (SELECT COUNT(row) FROM #TempArticleListTable);
 
-- Print the number of articles info.
PRINT CHAR(13) + 'Number of Articles: ' + CAST(@rowCount AS VARCHAR) +
CHAR(13) + CHAR(13) + 'List of updated Articles:-' + CHAR(13);
 
-- Declare and initialise an iterator.
DECLARE @i INT;
SET     @i = 1;
 
-- Iterate through the rows of the temp table.
WHILE (@i <= @rowCount)
BEGIN
     -- Get the article name and article ID from the temp table.
     SELECT @qualified_object_name = name  FROM #TempArticleListTable WHERE row = @i;
     SELECT @artid                 = artid FROM #TempArticleListTable WHERE row = @i;
 
     -- Update the identity ranges.
     EXEC sys.sp_MSrefresh_publisher_idrange @qualified_object_name, @subid, @artid, @ranges_needed, @refresh_check_constraint;
 
     -- Print the name of article that has just been updated.
     PRINT @qualified_object_name;
     
     -- Increment the iteration count.
     SET @i = @i  + 1;
END

Alright, let’s walk through what this is doing.

First of all, the variables that are ultimately passed to the sp_MSrefresh_publisher_idrange stored procedure are declared and assigned. These are the same arguments that were covered in the previous section.

The pubid and subid variables need to be updated before you run the script in order for it to work properly.

Next, a temporary table is populated with a distinct list of all of the merge articles for the specified publication ID. The number of rows in the temporary table is then calculated and printed for reference.

A while loop is used to iterate through each row of the temporary table.

For every loop iteration, the qualified_object_name and artid variables are set based on the current merge article row.

The sp_MSrefresh_publisher_idrange stored procedure is then called, passing along the appropriate arguments.

Lastly, the value of the qualified_object_name variable is printed for reference and the loop continues until all of the merge article records have been processed.

After the script has completed, the identity range check constraint for every merge article in your publication database should be updated and should now match the identity range allocations table in your distribution database!

Summary

I have documented two ways of resolving the problem where identity range check constraints are out of sync with identity range allocations within a SQL Server Merge Replication setup.

I’ve shown how to correct the issue on an article by article basis and also how to correct the issue globally for all merge articles using a SQL script.

In closing, I hope you found this article helpful and that it provides the solution you are looking for.