Duplicate Indentity Values in Primary Key Column

  • 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

     

  • 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?

  • 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.

  • do you have any indexes with IGNORE_DUP_KEY  option in it?


    * Noel

  • 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]

  • 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.

  • 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]

  • 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.

  • 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]

  • 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?

  • 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]

  • 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.

  • 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]

  • 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.

  • 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