During the course of upgrading a search application database from SharePoint 2010 to SharePoint 2013 we encountered an error in PowerShell:
Microsoft.SharePoint.Upgrade.SPUpgradeException: Action 18.104.22.168 of Microsoft.Office.Server.Search.Upgrade.SearchAdminDatabaseSequence failed
And we traced it back down to this error in the ULS:
SqlError: ‘The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.MSSSchemaPropertyMappingsPending’ and the index name ‘IX_MSSSchemaPropertyMappingsPending’. The duplicate key value is …
The cause of this appears to be that the upgrade process is attempting to create a unique key on the table, but the values in the table are non-unique which results in an error. The solution is to remove the duplicate keys, which can be done via this script:
WITH CTE AS(select *, rn=row_number() over (partition by CrawledPropertyId, PID order by CrawledPropertyId, PID) FROM MSSSchemaPropertyMappingsPending) DELETE FROM CTE WHERE RN > 1
We also had an issue where, by the time we had this error, the Search Service Application was half created and difficult to delete. We ultimately had to get the GUID of the search service application and use stsadm.exe -o deleteconfigurationobject and then delete the SPDatabase objects associated with the half created service using Get-SPDatabase and then calling .Delete() off each SPDatabase object.