February 25, 2004 at 10:35 am
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
February 25, 2004 at 1:20 pm
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?
February 26, 2004 at 6:09 am
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.
February 26, 2004 at 7:13 am
do you have any indexes with IGNORE_DUP_KEY option in it?
* Noel
February 26, 2004 at 7:26 am
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]
February 26, 2004 at 7:31 am
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.
February 26, 2004 at 7:37 am
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]
February 26, 2004 at 7:42 am
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.
February 26, 2004 at 7:46 am
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]
February 26, 2004 at 7:52 am
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?
February 26, 2004 at 8:04 am
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]
February 26, 2004 at 8:15 am
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.
February 28, 2004 at 3:27 pm
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]
February 29, 2004 at 11:27 pm
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.
November 2, 2004 at 10:11 am
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply