How to detect, repair and delete Orphan objects in SharePoint?

Category: Common Problems faced during Upgrade from SPS2003 to MOSS2007

Problem:
How to detect Orphan objects? How to remove or delete Orphans from the system?

Scenario:

We are doing Gradual upgrade approach for upgrading SPS 2003 to SharePoint 2007. During the root site upgrade, it failed saying that there are orphans in the sites.

Solution:

What is an Orphan Site?
An Orphaned Site is where SharePoint only has partial information and not a complete set of data for a given site collection in your Windows SharePoint Services or SharePoint Portal Server content databases or configuration databases. The site may in fact still be viewable via the browser, but you may notice that many things are broken. 99% of the cases I have seen though, the orphaned site is just unavailable.

How to detect Orphan Site?
To detect orphaned items, use the Stsadm command:

stsadm -o databaserepair -url http:// -databasename

This would return a XML with the orphan sites (if they exist) details as mentioned below:








Refer to ‘http://technet.microsoft.com/en-us/library/cc263282(TechNet.10).aspx’ for details on this stsadm command.

How to delete or remove orphan site?
To delete orphaned items, use the following stsadm command:

stsadm -o databaserepair -url http:// -databasename -deletecorruption

This command repairs corruption in the content database but sometimes it can’t remove corruption in the configuration database.

Here Spsadm tool comes into picture. Spsadm cleans up orphaned items in the configuration database in Sharepoint server 2003.

spsadm repairorphans http://Server1
Refer to ‘http://support.microsoft.com/kb/918742’ for more details on this command.

How to handle the worst kind of Orphan sites?

It happened to us in one of our upgrade phases. Our gradual upgrade was failing due to an Orphan site. We tried to repair the corrupted site using stsadm –databaserepair but it didn’t help. So I turned to SPSADM . That also didn’t help. The orphan site was not even getting deleted from the frontend.

So the only option was to somehow delete the site from the database. THOUGH THIS IS NOT A RECOMMENDED MICROSOFT APPROACH, we were left with only this option.

1) Find out the Site ID of the orphan site. You will get this from the orphan XML.
Check its entry in Webs table.
select * from webs where id = 'A7A18BB5-A483-4979-8038-0DA388CBC3E0'

For deleting an orphan site or web from the database, you have to delete its entry from webgroupmembership, webmembers & webs table, as shown below:

delete from webgroupmembership where webid= ‘A7A18BB5-A483-4979-8038-0DA388CBC3E0'

delete from webmembers where webid= ‘A7A18BB5-A483-4979-8038-0DA388CBC3E0'

delete from webs where id = 'A7A18BB5-A483-4979-8038-0DA388CBC3E0'

2) In our case, we had three orphan lists as well. You can see the lists in the xml (Output of stsadm –databaserepair command)

For deleting an orphan list from the database, delete its entry from Userdata, docs & aptb_lists sharepoint tables.

delete from aptb_lists where tp_id = '4202479A-706D-4416-ADCB-35410EAA7672' (corrupted List)

delete from aptb_lists where tp_id = 'B2A38B61-CC99-4528-A6B4-365019BB5B03' (corrupted List)

delete from aptb_lists where tp_id = 'FD65BF5F-D120-4A41-A2AB-63A6BA60C45' (corrupted List)

Similarly check for the list id entry in Docs & userdata and delete those entries from it.

***Either Stsadm -databaserepair or spsadm -repairorphans or deleting from database should solve the ORPHANED object problem for you.