Share via

Fix for TFS 2015 Upgrade error The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tbl_Workspace_Dataspace' and the index name 'IX_tbl_OwnerId_WorkspaceName_WorkspaceVersion_Type'

  • Article

If you are upgrading to TFS 2015 there is a very tiny chance you might hit this error:

[19:40:39.780]   System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tbl_Workspace_Dataspace' and the index name 'IX_tbl_OwnerId_WorkspaceName_WorkspaceVersion_Type'. The duplicate key value is (1, <GUID>, <WorkspaceName>, 1, 0).

[19:40:39.780]   Rebuilding index: IX_tbl_OwnerId_WorkspaceName_WorkspaceVersion_Type

[19:40:39.780]   ALTER INDEX [IX_tbl_OwnerId_WorkspaceName_WorkspaceVersion_Type] ON [dbo].[tbl_Workspace_Dataspace] REBUILD WITH(ONLINE=OFF, MAXDOP=0)

The workaround is to run the follow SQL Script on the TFS Collection database that failed to upgrade and then restart the upgrade job.

DECLARE @fallbackTfId AS UNIQUEIDENTIFIER
DECLARE @fallbackId AS INT

SELECT  TOP(1)
@fallbackTfId = vcim.TeamFoundationId,
@fallbackId = vcim.IdentityId
FROM    tbl_VCIdentityMap vcim
WHERE   PartitionId = 1
ORDER BY vcim.IdentityId ASC

UPDATE  wd
SET     WorkspaceName = SUBSTRING(wd.WorkspaceName, 1, 54) + '_TF' + SUBSTRING(CONVERT(NVARCHAR(36), NEWID()), 1, 7)
FROM    tbl_Workspace_Dataspace wd
JOIN    tbl_Workspace w
ON      w.PartitionId = wd.PartitionId
AND w.WorkspaceId = wd.WorkspaceId
AND w.OwnerId <> @fallbackId
WHERE   wd.PartitionId = 1
AND wd.OwnerId = @fallbackTfId
AND wd.WorkspaceName = w.WorkspaceName

...

Then restart the job by opening the TFS Administration Console, select the Team Project Collection tab, select the collection that is Offline, select the Status tab, select the ApplyPatch activity, and then click Rerun Job.

This issue has been fixed in TFS 2016 RTM.