none
Duplicate key error while Rebuilding partition RRS feed

  • Question

  • I am getting below error even though I don't have unique key on any of indexes on the table.

    Command I m running..

    Alter Index all on  FactDetailMaster rebuild partition = 70 with (online=on)

    Error..

    Msg 1505, Level 16, State 1, Line 3
    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FactDetailMaster' and the index name 'idxFDM_Clustered1'. The duplicate key value is (120326948, Sep 12 2017 12:00AM, 0).
    The statement has been terminated.

    Index script for reference...

    CREATE CLUSTERED INDEX [idxFDM_Clustered1] ON [dbo].[FactDetailMaster]
    (
    [OrderNumber] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
    GO


    Mahesh

    Saturday, November 11, 2017 1:05 PM

All replies

  • The duplicate key error message looks odd. It lists 3 values in the key but your index has only one explicit key column. I would expect the error to list no more than 2 values, OrderNumber and the (apparent) datetime partitioning column value.

    Not sure what might be going on. Are your DBCCs clean? Are there concurrent insert/update during the rebuild?

    Please edit your question to add your CREATE TABLE DDL and add the ON clause of the index create. Also, run the query below to ensure partition #70 is returned.

    SELECT $PARTITION.PF_Test('2017-09-12T00:00:00') AS ExpectPartitionNumber70;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, November 11, 2017 2:15 PM
    Moderator
  • Hello Mahesh,

    The duplicate key value is (120326948, Sep 12 2017 12:00AM, 0).

    it contains 3 column values and your index definition has only one column.

    Can you please query the table and let us know the duplicate value rows or you need to change the definition of index.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Saturday, November 11, 2017 2:31 PM
  • I would guess that the third column is the uniquifier. Note that the error message talks about unique index, and yet the index is defined as non-unique.

    It smells coruption long way. It could be that the source is corrupted, but bad memory or similar could introduce the corruption during the operation.

    What does this return?

    SELECT page_verify_option_desc FROM sys.databases WHERE name =  <nameofdatabase>


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 11, 2017 3:40 PM

  • SELECT $PARTITION.PF_Test('2017-09-12T00:00:00') AS ExpectPartitionNumber70;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Yes it did return 70 as should be.

    We do have records duplicate on Ordernunber and that date on which it is partitioned. But we don't have any UNIQUE indexes at all.  The error is weird!?

    We recently created that new table with partition couple of weeks ago, there was no schema change or anything  after that to perform dbcc clean - I was just Rebuilding of that one partition as it showed up high in fragmentation.

    There is no data load happening during my Rebuild - I tried again a while ago as well.


    Mahesh

    Saturday, November 11, 2017 3:44 PM
  • Hello Mahesh,

    The duplicate key value is (120326948, Sep 12 2017 12:00AM, 0).

    it contains 3 column values and your index definition has only one column.

    Can you please query the table and let us know the duplicate value rows or you need to change the definition of index.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Yes we have duplicate  records with those values and many more as it is a Detail table there will be many records for a given OrderNumber. The issue is we do not have UNIQUE index on the table.


    Mahesh

    Saturday, November 11, 2017 3:47 PM
  • We do have records duplicate on Ordernunber and that date on which it is partitioned. But we don't have any UNIQUE indexes at all.  The error is weird!?

    I'm pretty sure Erland is right about uniqueifier corruption. I considered that after my post. Remember that all clustered index keys are unique internally because those are used as a row locator. When more than one row exists with the same clustered index key, SQL Server adds a sequence number uniqueifier to make the key unique. So the unique key SQL Server is complaining about is actually OrderNumber, Date partitioning column, and uniqueifier (zero).

    I would have expected DBCC to detect this problem. To correct it, you could switch partition 70 into and identical aligned staging table, drop and recreate the clustered index, and switch the partition back into the main table.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Saturday, November 11, 2017 3:59 PM
    Moderator
  • We do have records duplicate on Ordernunber and that date on which it is partitioned. But we don't have any UNIQUE indexes at all.  The error is weird!?
    I mentioned this in my post, but maybe I should clarify: all clustered index are unique. If you create them as non-unique, SQL Server adds a hidden four-byte uniquifier to the index key. That explains why you can get a unique-key violation. But obviously, something is wrong if the same uniquifier appears multiple times. That smells corruption.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Uwe RickenMVP Sunday, November 12, 2017 9:42 AM
    Saturday, November 11, 2017 4:40 PM
  • We ran DBCC  HECKTABLE (factdetailmaster) WITH NO_INFOMSGS, ALL_ERRORMSGS,  DATA_PURITY, and found no corruption or error.



    Mahesh

    Sunday, November 12, 2017 5:13 PM
  • You might try changing the partition to DATA_COMPRESSION=NONE and trying again.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, November 12, 2017 5:42 PM
    Moderator
  • And/Or add his own uniquifier, an identity or sequence.

    Really, you want to have your own unique PK on any table, this is a relational database, and presumably an acceptable data model will have this.

    Josh


    • Edited by JRStern Sunday, November 12, 2017 7:54 PM
    Sunday, November 12, 2017 7:53 PM