Identity Key Problem and DBCC CHECKIDENT

A colleague today said they received an error like this:

Msg 2627, Level 14, State 1, Procedure SP_name, Line 33
Violation of PRIMARY KEY constraint ‘PK_tableName_log’. Cannot insert duplicate key in object ‘dbo.tableName_log’. The duplicate key value is (19).

I’ve interpreted the above error message as follows:

dbo.tabloAdi_log has a Primary Key Constraint named PK_tabloAdi_log. In the column defined as PK, the value of “19” is tried to be inserted again while its present on this table.

First I found out which is the Primary Key column in this table. Then I looked at whether the IDENTITY property of this column is open. Then I checked the stored procedure that tried to insert a record into this column. Everything seemed right, the stored procedure was not trying to INSERT a value in the corresponding PK field, so the next Identity value had to be generated automatically.

I then checked the Identity value that was automatically generated in this table with the following command:

The result of the above command returned 19. So it’s the same value I got in my error message. SQL Server was trying to create a value of 19 in the PK field in this table, but I was getting an error because the table already had this value.

Then I found the largest value in the table with the following command:

The result of the above command is 13617043.

I haven’t found it yet, but somehow a transaction had changed the SEED of the table and the newly created Identity values continued from 19. That was the cause of the error. With the following command I changed the value of SEED to continue from 13617044.

And our problem has been solved.

You may want to read below articles:

“Identitiy Insert On SQL Server”,

“How To Set Identity Property to the Current Column in the Table”