Error “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found…” during GET LATEST in 2008

  • Article

Ladislau Szomoru (one of our Escalation Engineers in Budapest) brings us today’s note about a problem some users are seeing in TFS 2008 Source Code Control. There is a specific situation emerging that is causing some customers to see the following error message on a GET LATEST:

 

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.#requestedVersions’ and the index name ‘IX_requestedVersions_ItemId’

At the same time the following event gets logged on the Team Foundation Server application tier machine in the Application Event Log:

Detailed Message: TF14105: An exception occurred in the Team Foundation Source Control System.
Web Request Details
Url: [method: POST]
User Agent: Team Foundation (devenv.exe, 9.0.30729.1)
Headers: Content-Length=640&Content-Type=application%2fsoap%2bxml%3b+charset%3dutf-8&Accept-Encoding=gzip&Accept-Language=nb-NO&Expect=100-continue&Host=globe-dev2%3a8080&User-Agent=Team+Foundation+(devenv.exe%2c+9.0.30729.1)&X-TFS-Version=1.0.0.0&X-TFS-Session=6cc91dcd-1da6-4f69-a8f9-97e346098b39
Path: /VersionControl/v1.0/repository.asmx
Local Request: False
Host Address: xxx.xxx.xxx.xxx
User: <DOMAIN>\<USER> [authentication type: NTLM]

Exception Message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.#requestedVersions__________________________________________________________________________________________________000000001081' and the index name 'IX_requestedVersions_ItemId'. The duplicate key value is ( 100 ).
The statement has been terminated. (type SqlException)
SQL Exception Class: 16
SQL Exception Number: 1505
SQL Exception Procedure:
SQL Exception Line Number: 632
SQL Exception Server: <SQLSVR_NAME>
SQL Exception State: 1
SQL Error(s):
SQL Error[1]: System.Data.SqlClient.SqlError: The statement has been terminated.
Class: 0
Number: 3621
Server: TFSERVER
Source: .Net SqlClient Data Provider
State: 0
Procedure:
Line Number: 1

 

The error is caused by a data inconsistency due to which one or more version control items exist twice at the latest version.

The Team Foundation Server product group has developed a SQL script which resolves some data inconsistency cases. We are not posting the SQL script here as it must be customized for each and every case. In order to confirm that you are running into the same issue, we would like to ask you to run the following SQL script on your TfsVersionControl database from inside SQL Server Management Studio:

USE TfsVersionControl
SELECT ItemId FROM tbl_Version WHERE VersionTo = 2147483647 GROUP BY ItemId HAVING COUNT(*) > 1

We are looking for instances where there is >1 occurrence of the same item id registered as being at latest version (VersionTo = 2147483647). If this query returns any items (no return means you’re good) we would like to ask you to please contact Customer Services and Support and reference this BLOG post – we will help you out.

 

Technorati Tags: Team Foundation Server,Source Code Control