The maximum system-generated unique value for a duplicate group was exceeded

Meddelandet nedan ser man inte så ofta, tack och lov. Dock har jag nu stött på det.

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

Detta inträffade i en gigantisk tabell, partitionerad på period (år + månad). Det innebär ett klustrat index på i det här fallet enbart period, som förstås inte är ett unikt värde. Alltså måste SQL Server lägga på en sk Uniqueifier för att det klustrade indexet skall bli unikt.

Om detta kan man läsa mer här och som framgår där och från Books Online :

If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.

Som framgått av länken ovan så finns det en maxgräns för 4 294 967 296 innan man skall få detta meddelandet. Frågan kommer då, “Har vi nått denna gräns och vilken partition är det egentligen som spökar ?”.

Med hjälp av denna fråga ser man för vilken partition som vi nått maxgränsen :

 SELECT 
           p.partition_number AS [PartitionNumber],
           p.data_compression_desc AS [DataCompression],
           prv.value AS [RightBoundaryValue],
           fg.name AS [FileGroupName],
           CAST(p.rows AS float) AS [RowCount],
           p.object_id,
           p.partition_id,
           idx.name AS Index_name
           
FROM
           sys.tables AS tbl
           INNER JOIN sys.indexes AS idx ON 
		idx.object_id = tbl.object_id AND 
		idx.type_desc = 'CLUSTERED'
           INNER JOIN sys.partitions AS p ON 
		p.object_id=CAST(tbl.object_id AS int) AND 
		p.index_id=idx.index_id
           INNER JOIN sys.indexes AS indx ON 
		p.object_id = indx.object_id AND 
		p.index_id = indx.index_id
           LEFT OUTER JOIN sys.destination_data_spaces AS dds ON 
		dds.partition_scheme_id = indx.data_space_id and 
		dds.destination_id = p.partition_number
           LEFT OUTER JOIN sys.partition_schemes AS ps ON 
		ps.data_space_id = indx.data_space_id
           LEFT OUTER JOIN sys.partition_range_values AS prv ON 
		prv.boundary_id = p.partition_number and 
		prv.function_id = ps.function_id
           LEFT OUTER JOIN sys.filegroups AS fg ON  
		fg.data_space_id = dds.data_space_id or 
		fg.data_space_id = indx.data_space_id

Detta visar att vår felande partition innehåller ca 700 000 rader och är alltså långt från maxgränsen på 4 294 967 296.

Första testen för att fixa detta problem är att enbart bygga om den felande partitionen för att tjäna tid.

ALTER INDEX [indexnamn] ON [dbo].[tabellnamn] REBUILD PARTITION = partitionsnr

Detta hjälper inte.

Nästa test är att bygga om hela det klustrade indexet.

ALTER INDEX [indexnamn] ON [dbo].[tabellnamn] REBUILD PARTITION = ALL

Tyvärr hjälper inte detta heller.

Till slut kommer här lösningen, som innebär att man använder en temptabell för att flytta allt data från partitionen, tömma dessa i orginaltabellen för att till slut läsa tillbaka allt.

SELECT *
INTO tmp_201212
FROM Fact
WHERE Period = 201212
 
DELETE
FROM Fact
WHERE Period = 201212
 
INSERT INTO Fact
(...)
SELECT *
FROM tmp_201212