none
Getting MSG 666: The maximum system-generated unique value for a duplicate group was exceeded for index

    Question

  • I'm getting an error in my SQL Server 2k8 SP1 Enteprise edition engine when running a query:

    Msg 666, Level 16, State 2, Line 1
    The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422223771074560. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

    In this case all my indexes are non-clustered.  It's a view i'm running this aggregation on, where i'm joing on three tables. THe rowsize for each of the tables:


    Table1:  2,210,937,396 rows
    Table2:  73,775,650    rows
    Table3:  17,125 rows

    Which will keep growing by 1-10% each week for each table.

    I'm not sure what I can do here to resolve this.


    • Moved by Tom PhillipsModerator Monday, March 08, 2010 5:46 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Monday, March 08, 2010 4:08 PM

Answers

All replies

  • Please post your code here , there must be something wrong with the code loop, because it is nearly impossible to  see this error

    check this out

    http://robboek.com/2009/02/13/sql-server-uniqueifier-values-and-the-number-of-the-beast/


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Monday, March 08, 2010 5:09 PM
  • Hello,

    The limit for duplicate entries is 4,294,967,296.

    You're pretty close.

    Adam


    Dibble and dabble but please don't babble.
    Monday, March 08, 2010 7:04 PM
  • Here is the code pretty simple aggregate function:

    SELECT

     

    MIN(DateAdded) as MinDateAdded

     

    ,COUNT(DISTINCT HashVal) as CountHashVal

     

    ,Category

     

    ,isFree

     

    ,BitType

    INTO

     

    AppDownload

    from

     

    dbo.v_Lvl1AggregateData (nolock)

    group

     

    by AppId

     

    ,Category

     

    ,BitType


    again, w/ the number of rows, not sure how I can get around this.
    B/c AppId is sequential, I'm thinking of doing this in batches w/ ranges of AppId ie:

    SELECT

     

    MIN(DateAdded) as MinDateAdded

     

    ,COUNT(DISTINCT HashVal) as CountHashVal

     

    ,Category

     

    ,isFree

     

    ,BitType

    INTO

     

    AppDownload1thru2000

    from

     

    dbo.v_Lvl1AggregateData (nolock)

    Where AppId between 1 and 2000

    group

     

    by AppId

     

    ,Category

     

    ,BitType


    Not sure if this would work or not.

    Monday, March 08, 2010 8:33 PM
  • Sorry here is the code:
    SELECT MIN(DateAdded) as MinDateAdded
                ,COUNT(DISTINCT HashVal) as CountHashVal
                ,Category
                ,isFree
                ,BitType
    INTO AppDownload
    from dbo.v_Lvl1AggregateData (nolock)
    group by AppId
                ,Category
                ,BitType

    This one has no spacing.  I initially copied right out of SMSS. 

    If i did in batches with range of appIds, i think this might help me, not sure:

    SELECT MIN(DateAdded) as MinDateAdded
                ,COUNT(DISTINCT HashVal) as CountHashVal
                ,Category
                ,isFree
                ,BitType
    INTO AppDownload1Thru2000
    from dbo.v_Lvl1AggregateData (nolock)
    WHERE AppId between 1 and 2000
    group by AppId
                ,Category
                ,BitType
    Monday, March 08, 2010 8:35 PM
  • yeah, i realized that when I saw the above link posted by Mohammad Sufian.  Only thing is, how can i get around this.  By doing this in batches?  SQL has to somehow be able to support sizes of much larger values.  Especially in the age we're living in w/ PetaBytes of data now a days.
    Monday, March 08, 2010 8:37 PM
  • Anson1270,

    Can you try your query with the distinct aggregation alone?

    SELECT COUNT(DISTINCT HashVal) as CountHashVal
                ,Category
                ,isFree
                ,BitType
    INTO AppDownload
    from dbo.v_Lvl1AggregateData (nolock)
    group by AppId
                ,Category
                ,BitType
    GO

    AMB
    Monday, March 08, 2010 8:45 PM
    Moderator
  • Well I did this:

    SELECT DateAdded
         ,COUNT(DISTINCT HashVal) as CountHashVal
                ,Category
                ,BitType
         ,appId
    INTO AppDownload
    from dbo.v_Lvl1AggregateData (nolock)
    group by AppId
                ,Category
                ,BitType
         ,dateadded


    There was no issue.  Took a long time, but it still worked. 
    When I did

    SELECT Min(DateAdded) as MinDateAdded
                ,Category
                ,BitType
         ,appId
    INTO AppDownload
    from dbo.v_Lvl1AggregateData (nolock)
    group by AppId
                ,Category
                ,BitType

    That also worked. 
    Monday, March 08, 2010 8:50 PM
  • Also sorry field isFree is the same as BitType.  I just renamed the column from isFRee to BitType.
    Monday, March 08, 2010 8:52 PM
  • You said that you are using a SQL Server 2008 SP1 instance, right?

    If so, then I will consider this an error regression.

    FIX: Error message when you run a query in SQL Server 2005: "Cannot insert duplicate key row in object <TableName> with unique index <IndexName>"
    http://support.microsoft.com/kb/937533

    You can open a case with CSS, or you can file the error on www.connect.microsoft.com


    AMB

    Monday, March 08, 2010 9:00 PM
    Moderator
  • Yeah looks like.  But I see this error in 2k5, and they seem to have a hotfix for it.  Wonder why fix was not applied to 2k8.
    Monday, March 08, 2010 9:21 PM
  • I am going to ask in the private newsgroup and I'll get back to you here.


    AMB
    Monday, March 08, 2010 9:23 PM
    Moderator
  • Did you ever find anything out in the private newsgroups?
    Sunday, February 12, 2012 9:24 PM
  • What is your SQL Server version?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, February 12, 2012 9:31 PM
    Moderator
  • I have experienced this error this morning on a SQ L 2K5 box running SP3, build 4266.  

    I believed that this issue should have been fixed by SP3, does anyone know otherwise?

    Wednesday, March 13, 2013 9:19 PM
  • 9.00.4266

    Thanks

    Wednesday, March 13, 2013 11:04 PM
  • You're missing the latest Service Pack for your version of SQL Server. I would start from installing it

    http://blogs.msdn.com/b/pamitt/p/sql_version_history.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, March 14, 2013 1:43 AM
    Moderator