Insert into replicated table fails - identity range check

Question

I'm trying to insert a few thousand rows into a table in a database that is replicated across two servers. From either the publisher or the subscriber, I get the same error:

Msg 548, Level 16, State 2, Line 1
The insert failed. It conflicted with an identity range check constraint in database 'XXX', replicated table 'dbo.NODE_ATTRIB_RSLT', column 'ID'. 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.

Checking the constraint on the table, it seems to me like I should be able to insert at least 1000 rows at a time before running into issues. However, I get the same error when trying to insert just a few tens of rows!

Here's how I'm trying to insert data:

insert into NODE_ATTRIB_RSLT 
([NODE_ID]
      ,[ATTRIB_ID]
      ,[STATE_ID]
      ,[PLAN_REVISION_ID]
      ,[TIMESTAMP]
      ,[VALUE]
      ,[VALUE_TEXT]
      ,[LAST_MODIFIED]) 
SELECT [NODE_ID]
          ,[ATTRIB_ID]
          ,[STATE_ID]
          ,[PLAN_REVISION_ID]
          ,[TIMESTAMP]
          ,[VALUE]
          ,[VALUE_TEXT]
          ,[LAST_MODIFIED]   FROM [NODE_ATTRIB_RSLT_TEMP]

The PK column is an autogenerated identity called ID. To try to insert fewer rows at a time I've added a WHERE clause at the end of the select like so:

WHERE ID >= 1000 and ID <1100

but to no avail.

Running sp_adjustpublisheridentityrange on the Publisher executes successfully but has no effect.

How can I fix this problem with inserts? How can I modify the ranges of the indentity range contraints to a more reasonable level while leaving the replication running?

Solution 1

I think I worked out what the problem was.

Looking at the properties for the replicated table, it had the standard default identity range of 10000 for the Publisher and 1000 for the Subcriber.

However, checking the identity constraint on the actual table (using SP_HELPCONSTRAINT 'node_attrib_rslt') revealed that there was only a pool of 1000 IDs on both servers. This made the bulk insert fail even when I restricted the number of rows to insert - I'm guessing SQL Server doesn't even get that far when it checks the constraint when running an INSERT INTO.

To fix it I had to do several things:

Change the identity range of the table. I set it up to 20K for both Publisher and Subcriber.

1. On the Publisher, expand Replication --> Local Publications
2. Right-click the particular subscription and choose Properties.
3. Select the Articles page.
4. Highlight the appropriate Table.
5. Click on the Article Properties 'button', and choose 'Set Properties of Highlighted Table Article'.
6. In the Article Properties window, look for Identity Range Management options.
7. Change the appropriate values.
8. Press OK and OK on the dialog windows.

Run the sp_adjustpublisheridentityrange stored proc on the Publisher.

1. New query window on the server
2. Choose the correct database
3. Execute sp_adjustpublisheridentityrange @table_name = 'node_attrib_rslt'

From the subcriber, force-synchronise the servers.

1. On the Subscriber, expand Replication --> Local Subcriptions
2. Right-click the particular subscription and choose View Subscription Status.
3. In the dialog that appears, press the monitor button.
4. In the Replication Monitor window that appears, expand the particular Publisher in the left hand pane.
5. Click on the Subcription to edit.
6. In the right hand pane, right-click on the subcription status and choose Start Synchronising.
7. The status should update to 'Synchronising' while it does its thing.
8. After it's finished, click on the 'Warnings and Agents' tab. I had a 'Snapshot Agent' listed in the lower pane. Right click on that Agent and start it. After it had been running for a while, the change of properties on the server should have migrated to the client.

Maybe: insert some test rows into the table.

Edit: I've had to do this task again recently, and the constraint on the table would not update until I inserted a bunch of dummy data into the table so as to exhaust the default constraint. Then I resyncronised the servers, and the constraint was updated to the new value.

After that, checking the identity constraint revealed that I finally had a 20K ID range to insert with on both the Publisher and the Subcriber.

Solution 2

I had this exact same issue, and the above solution didn't do anything for me. Instead what ended up solving the issue was by setting the new, larger identity ranges on the tables in the publication, and then dropping the identity constraints on the table And then finally running this command that sets the current identity.

DBCC CHECKIDENT ('TableName', RESEED, 1000000000);

Instead of setting the value to 1000000000, the constraint is created again, and set to the correct identity range value currently specified on the table in the publication. It looks like the CHECKIDENT command forces the constraint to be updated somehow.

The above solution worked for me, but was actually my attempt at just dropping the constraints and setting the publisher and subscriber to use different identity ranges so they would be able to insert rows in the tables. Fortunately the CHECKIDENT seemed to refresh the constraint, something i originally expected the sp_adjustpublisheridentityrange stored procedure to do - except it did nothing.

I ran the above command on both the publisher and the subscriber.

Comments

[Dejan Dozet]

I can't agree with this solution and this didn't help at all, actually this have changed identity value to be 1000000000 and constraints remained the same.