1

See below timeline:

1 - 04:58 (Server A) - REORG of PK index on Table1 in DB_XXX started.

2 - 05:00 (Server A) - Create SNAPManager copy of DB_XXX

3 - 05:01 (Server B) - SNAPManager restore of DB_XXX_Copy.

4 - 05:11 (Server A) - REORG of PK index on Table1 in DB_XXX Complete.

Note: SNAP copy taken mid REORG.

5 - 05:25 (Server B) - CREATE NONCLUSTERED INDEX on Table1 failed with the following error: "CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Table1' and the index name 'NonClusteredIndex'. The duplicate key value is (111, 222)."

  • Firstly, The error suggests the index being created is a UNIQUE INDEX, however the error is generated when running a CREATE NONCLUSTERED statement.
  • Secondly, The Duplicate Key error references 2 values (111,222), but the PK is on one column and the NON CLUSTERED INDEX is on one column.

The only thing I can think of is that because the SNAP copy was taken in the middle of a REORG, the PK on this table was in an inconsistent state on the copy? Here is a link to a similar issue: https://stackoverflow.com/questions/3398348/unique-index-error-when-creating-non-unique-index-sql-server.

Is anyone able to explain or verify this behaviour?

Thanks

Improve this question
7
  • UNIQUE and CLUSTERED/NONCLUSTERED are two separate dimensions, so I'm not sure why you're thinking the NONCLUSTERED somehow automatically implies something about uniqueness. Commented Dec 14, 2016 at 11:00
  • So did you run DBCC CHECKTABLE as mentioned in the other question, and did it give your table a clean bill of health? Commented Dec 14, 2016 at 11:39
  • No, in this case we re ran the SNAP copy / restore process. This time the REORG would not have been running. The CREATE NONCLUSTERED index was then successful on the copy. Commented Dec 14, 2016 at 11:44
  • Note that anything capable of making a table inconsistent is considered a serious bug by the SQL Server development team, irrespective of what statements you run. Even in the middle of an index reorganization that's inserting rows, creating an index should not fail. If you can repro this and if DBCC CHECKTABLE does indicate a problem, you've got something that you might want to open a Connect issue for, even if you can work around it. (If there is no CHECKTABLE issue and the SNAP copy is just doing it wrong, that's another matter.) Commented Dec 14, 2016 at 11:47
  • You cannot create an index while a reorganization is running on the same table. They take incompatible locks (schema modification locks). The stated timeline shows that they weren't done at the same time as well. Commented Dec 14, 2016 at 23:27
Related questions
1
SQL Server unique index allowing duplicates
68
Unable to create index because of duplicate that doesn't exist?
2
Unique Index Error when creating non-unique index - SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.