SQLServerWiki

“The Only Thing That Is Constant Is Change”

Issues with INSERT due to IDENTITY Constraint in Transactional Replication.

Posted by database-wiki on April 11, 2016

IDENTITY Constraint Causing Issue in Transactional Replication with Immediate Updatable subscription.

You might get an error while inserting a records in a table on subscriber that has an IDENTITY column:

Error 548, Severity: 16, State: 2
The insert failed. It conflicted with an identity range check constraint in database ‘KEYDATABASE’,
replicated table ‘dbo.KEYTABLE’, column ‘nodeid’.
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.

-> This error means that the INSERT statement is trying to insert a value for the column “NODEID” in the table “KEYTABLE” that is out of IDENTITY range managed by replication.

Things to check to confirm the above statement:

1. Checked the identity range for replication for this article <table_name>.
2. Then SELECT MAX(<columns_name>) from table_name
3. Check the range Threshold percentage(default is 80%).
4. Check DBCC CHECKIDENT (‘<table_name>’,noreseed)
Checking identity information: current identity value ‘<some value>’, current column value ‘<some_value>’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
5. If you see this value as out-of-range (checked in the step 1) then this is the cause for INSERT failure.

To resolve this problem, you can try perform following steps:

1. Change the range to match the values in the table.
2. Run DBCC CHECKIDENT with RESEED option at subscriber to fix this.
3. Execute sp_adjustpublisheridentityrange (see if that helps)
4. Then restart the distribution agent.
5. If nothing works Reinitialize subscription would be required.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: