Monday, October 10, 2016

Solving Sharepoint Configuration DB orphans

Hi, it took some efforts to get out of this one ;)

Symptoms : your sharepoint environment explodes when the wizard is launched, with an error message saying :

"The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_Objects_Objects". The conflict occurred in database "SharePoint_Config", table "dbo.Objects", column 'Id'

No recovering from this is possible, as there data written but no rollback, you get various error messages like "Invalid object name 'SiteMapVisible'" , but you just lost your sharepoint platform.
Restoring the config DB is the only solution.

Solution :

 Locate any orphan using SQL :

use SharePoint_Config select distinct Id from dbo.Objects where ParentId not in (Select Id from dbo.Objects) 

 (find the IDs of object not having any parents objects == orphans)

Action :
Warning : this not supported by MS and can destroy your sharepoint, backup first! 

Copy and paste the IDs into a string array, then delete them :

 Add-PsSnapin Microsoft.SharePoint.PowerShell -EA 0

$array = @("71AF3D3B-63D5-4....", "....")   # our IDs from (1)

foreach ($element in $array) {
    stsadm.exe -o deleteconfigurationobject -id $element

A SQL delete is probably just as good, but i lose nothing using the deleconfig command, which perhaps does more.