The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d. Dropping and re-creating the index may resolve this; otherwise, use another clustering key

Many of you may have encountered such a critical situation like some of the Table was working fine until just last minute without any Err and suddenly you may have faced an Err shown below so called Error 666

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 %I64d.
Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

What does mean ?

According to MSDN - "Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following"

• Can be used for frequently used queries.
• Provide a high degree of uniqueness
• Can be used in range queries

If the clustered index is not created with the UNIQUE property forced to it!. So what ?

Here is the internal thing happens - "The Database Engine automatically adds a 4-byte UNIQUIFIER column to the table. When it is required, the Database Engine automatically adds a UNIQUIFIER value to a row to make each key unique. The value starts with 0"

But, This column and it's values are used internally and cannot be seen or accessed by the users by accessing the table just like that.

You may ask now - Why do we need to worry about this situation ?

Let's have some scenario

USE [MyDatabase1]

GO

/*Table created with TWO column*/

CREATE TABLE MyTable1

(

Id INT, Names VARCHAR(20) 

)

GO

/*Clustered Non-Unique key created on Id column*/

CREATE CLUSTERED INDEX CI_Id ON MyTable1(Id)

GO

/*2 Records inserted*/

INSERT MyTable1

SELECT 1,'SQL' UNION ALL

SELECT 2,'SERVER'

GO

/*Checking the Page allocations of the Table*/

SELECT %%Lockres%% [KeyHashValue], sys.fn_physlocformatter(%%Physloc%%) [File:Page:Slot], * FROM MyTable1

/*Checking the specific page (Page ID: 232)*/

DBCC TRACEON(3604)

DBCC PAGE('MyDatabase1',1,232,3) WITH TABLERESULTS

But, It has lot of other Information. Right. let's simplify the data and remove rest of them

/*Creating a Temp table to have the about result. So, That we can filter-it out as needed*/

CREATE TABLE #Info(ParentObject VARCHAR(50), [Object] VARCHAR(100), Field VARCHAR(50), Value VARCHAR(1000))

INSERT #Info

EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

/*We don't need Header and Buffer Info for now*/

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT * FROM #Info

As you can see above, There is one more Internal column has been added "UNIQUIFIER" to force the Internal Uniqueness for the Clustered Key.

And, Both the "UNIQUIFIER" record has a value as "0". Because, There is No duplicate on Clustered key column "Id". So, There is no need for Nth duplicate indication on "UNIQUIFIER" column. Right!

/*Insert 4 More records with 2 Duplicate Data on "Id" column*/

INSERT MyTable1

SELECT 3,'SQL SERVER' UNION ALL

SELECT 3,'UNIQUIFIER' UNION ALL

SELECT 3,'CLUSTERED' UNION ALL

SELECT 4,'NON-UNIQUE'

GO

TRUNCATE TABLE #Info

INSERT #Info

EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT * FROM #Info WHERE Field IN ('UNIQUIFIER','Id','Names','KeyHashValue')

Can you guess ? What just happening ? Yes! - You are right!

We have inserted 4 New records and 3 of them duplicated (Id as "3"). Right ? So, The system wants to force the Unique value on "UNIQUIFIER" internal column. So, The "UNIQUIFIER" column have Nth duplicate indication!!

/*Deleted the records which Id column has "3"*/

DELETE MyTable1 WHERE Id = 3

GO

/*Inserted 3 records again*/

INSERT MyTable1

SELECT 3,'SQL SERVER' UNION ALL

SELECT 3,'UNIQUIFIER' UNION ALL

SELECT 3,'CLUSTERED'

GO

So, Can you guess - How many physical records will be there ? 

Let's explore here

SELECT * FROM MyTable1

GO

Yeah! We have 6 Records as expected.

What would be the "UNIQUIFIER" internal value the system have ?

TRUNCATE TABLE #Info

INSERT #Info

EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT * FROM #Info WHERE Field IN ('UNIQUIFIER','Id','Names','KeyHashValue')

Yes! We just have 6 Records (Slot 0 to Slot 5) only. But, Did you see the "UNIQUIFIER" Internal column value ?

It has been Increased! Because, we already had 2 duplicate detected on "Id" column. Now, It has increased since than. It's not Reset anymore even when records deleted. That's the reason why the Internal column getting Increased even If the actual duplicate records removed/deleted!

So, I hope you would have guessed, What would happen If these kind of duplicate records DELETED and INSERTED multiple time with huge volume (Millions of transactions), The "UNIQUIFIER" internal column still increase along with the DELETED duplicated data and It's not RESET anymore as I said earlier.

Ok. Guess what ?

We will get such an Error, If we perform such a transaction as explained above without resetting the UNIQUIFIER, sometimes we can reach it's upper limit "2147483648"

We will get such an Error If we INSERT 1 more duplicate record...

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 %I64d.
Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

The goal of this post is to give you an insight on how the UNIQUIFIER works and allow you to manually check for potential issue in your environment and avoiding such an error 666 and over come the situation

How to reset the value and unblock the situation ?

1.As per the Error suggests DROP and CREATE the Index
DROP INDEX MyTable1.CI_Id
GO
CREATE CLUSTERED INDEX CI_Id ON MyTable1(Id)
GO

Note: Rebuild Index will not help us

Check with the New page ID allocated and see whether the "UNIQUIFIER" value has been reset!

2. If still the Issue not been resolved

• Create a new table with same structure (MyTable1_New)
• Load the Data from an existing table (Using Import wizard Or Bulk Insert)
• Create the Clustered Index
• Archive/Remove the Old Table once make sure everything is resolved 

Thanks for your time!