Duplicate Indentity Values in Primary Key Column

Question

Post reply

Duplicate Indentity Values in Primary Key Column

prn9974

SSCrazy

Points: 2000
More actions
February 25, 2004 at 10:35 am

#84039

Hello All,
There are 2 things we will not expect to happen and for us they both happen simultaneously.

We have a table with the Primary Key column set as an Identity column. We get duplicate identity values, which is scary, and it does get inserted, violating the Primary Key Constraint. The error then appears when we run maintenance plans.

Can anyone please explain how/why this happens and how to avoid it? We logged a case with Microsoft and they are not sure of it yet!

Thanks in Advance

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

srankin Say Hey Kid Points: 673 More actions · Answer 1

Are you BCPing data into the table?

Do you manually insert data into the identity column with the IDENTITY_INSERT option set ON?

Are you resetting the Seed value of the Identity Column?

prn9974 SSCrazy Points: 2000 More actions · Answer 2

Thanks for the reply. We are not BCPing. Also the table is used by an ASP application and no manual insert is allowed. Identity_Insert is off for this table. We do not reset the identity. Not sure what happens though! There are several other tables with similar set up and there are no issues, but this table has had this problem 3 times in the last 2 weeks. Any light on this issue is highly appreciated.

noeld SSC Guru Points: 96590 More actions · Answer 3

do you have any indexes with IGNORE_DUP_KEY option in it?

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

hm... I always thought that defining a primarykey implies uniqueness

What are the results of dbcc checkdb?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

prn9974 SSCrazy Points: 2000 More actions · Answer 5

Thanks for the replies. We donot have any 'ignore dup' keys set on any index. More over this is a PK.

If I run DBCC checkDB it gives error on allocation of the index, with 'cannot find index id.....' error message. If I manually then go and delete the dupliacte entry and repair the table alloc, it works fine for sometime.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

I would carefully watch the app that is updating that table.

Hey, that will be my 3,000 post. Congrats to me

ROTFL

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

prn9974 SSCrazy Points: 2000 More actions · Answer 7

Thanks Frank and congrats on your 3000th post.

The app is not inserting values to this identity column. Thats why the whole confusion is. The column is Primary Key and it is an identity col with 'identity_insert' off.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Thanks!

Yes, I know the app won't explicitely insert values, but do you know what else it is doing?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

prn9974 SSCrazy Points: 2000 More actions · Answer 9

mmm, Its very tricky. We have looked through the app and seems to be ok. There are many SPs running which use '@@Identity', 'Scopr_Identity' and 'Ident_Current' in different places. Will this by any chance upset the identity seed?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

I would lie if I say I know for sure, but it might some thing to watch. Anyway I would expect SQL Server to throw an error for violating the PK constraint.

Good luck!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

prn9974 SSCrazy Points: 2000 More actions · Answer 11

Thanks Frank. I can even convince myself by saying identities can get funny based on several factors. But a primary key col to allow duplicate is very very very strange.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

Would you post the outcome of this when you have solved it?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 13

Run Profiler. and trace commands being executed by your ASP application. See the insert commands / SP fired by ASP application against your database.

If possible, put the table def and sql code in forum.

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.

prn9974 SSCrazy Points: 2000 More actions · Answer 14

Gentlemen,

Thanks for all your posts and help. I am sorry for being sloppy and posting this reply after a very long delay.

With regards to the issue that was originally posted, Microsoft has identified and provided solution to it. Thankfully the issue did not originate within SQL Server. We had issues with our Storage Area Network and that was creating issues with the cache. Thats the reason MS found in the duplicate identity. They also say that this is the same reason why the PK value also allowed duplicate value in the table.

We fixed the SANS configurations and all the issues are now sorted and gone. So I have to believe MS is right!! Though the issue is now closed, any suggestions/observations are welcome.

Many many thanks & kindest regards

Narayan Ramanathan