MOSS 2007 and Orphan objects – Part II

If the commands described on the first part of this post could not clean the orphan objects on your farm, means that your farm has “deep” orphan objects and you need to execute other series of steps before to execute the upgrade.

The best way to detect the deep orphan objects is to run the following SQL script on the SQL server that host the databases of your farm. The script is not mine and you can find it on the SharePoint Tips & Tricks blog of Microsoft TechNet site, as its author said you will execute the script at your own risk. I have executed it in several production environments where we have orphan objects and I have never had any problem.

You just need to copy and paste the following code on a Query window with read access to all databases and change the database name (in green) with the one of your Config database.

Use MSDB
Drop table orphanlist
CREATE TABLE [dbo].[orphanlist](
[farm] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[databasename] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteID] [uniqueidentifier] NULL,
[sitepath] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[type] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
drop table orphan_hopper  
declare  
@dbname as varchar(250), 
@cmdstr as varchar(2000), 
@dbid as varchar(250),
@configdb as varchar(250)  
/** only change the following line and nothing else, change spskills_config_db to your config db name **/
select @configdb = ‘MyConfigDB_MOSS’
/** Change nothing below this line **/
select @cmdstr = 
‘select distinct b.name as ”databasename”, b.id as ”dbid” into orphan_hopper 
from 
[‘ + @configdb + ‘].dbo.sitemap as a inner join   
[‘ + @configdb + ‘].dbo.objects as b on a.databaseid=b.id inner join   
[‘ + @configdb + ‘].dbo.objects as c on c.id=a.applicationid inner join   
[‘ + @configdb + ‘].dbo.objects as d on b.parentid=d.id inner join   
[‘ + @configdb + ‘].dbo.objects as e on d.parentid=e.id  ‘  
exec (@cmdstr)    
DECLARE DBCursor CURSOR For 
  Select databasename, dbid 
  From orphan_hopper 
OPEN DBCursor 
FETCH NEXT FROM DBCursor into @DBName, @dbid 
WHILE @@FETCH_STATUS =0 
BEGIN 
INSERT INTO orphanlist([Type], farm, databasename,[sitepath], SiteID) 
EXEC 
  (‘ 
select ”Potential ConfigDB orphan:” + ”’+@dbname+”’   as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],path as [sitepath], id as [SiteID] from [‘+@configdb+’].dbo.sitemap where id not in (select id from [‘+@dbname+’].dbo.sites) and databaseid = ”’+@dbid+”’ 
union 
select ”Potential ConfigDB orphan:” + ”’+@dbname+”’   as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],path as [sitepath], id as [SiteID] from [‘+@configdb+’].dbo.sitemap where id not in (select siteid from [‘+@dbname+’].dbo.webs where parentwebid is null) and databaseid = ”’+@dbid+”’ 
union 
select ”Potential ContentDB orphans:” + ”’+@dbname+”’ as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],fullurl as [sitepath], siteid as [SiteID] from [‘+@dbname+’].dbo.webs where parentwebid is null and siteid not in (select id from [‘+@configdb+’].dbo.sitemap where databaseid = ”’+@dbid+”’) 
union 
select ”Potential ContentDB orphan:” + ”’+@dbname+”’  as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],fullurl as [sitepath], siteid as [SiteID] from [‘+@dbname+’].dbo.webs where parentwebid is null and siteid not in (select id from [‘+@dbname+’].dbo.sites) 
‘) 
FETCH NEXT FROM DBCursor into @DBName, @dbid 
END 
CLOSE DBCursor 
DEALLOCATE DBCursor 
select * from orphanlist

This script will report a list of orphan objects found. On the screenshot /sites/myMOSS is the orphan found by SQL script:

The best way to clean this orphan site would be detach and attach the Content database to our farm, but this could eliminate our orphan site and in this case we suppose that we need to conserve it. I mean, myMOSS site is a site that we are using at this moment and we want to use it in the future, and for any reason that we don’t know it appears as orphan (the info related to this site on our Content and Config databases not concur).

In this case the best way is to make a backup of the site and restore it when we finish the detach/attach process, so the steps to execute would be the following:

Backup orphan object

Stsadm –o backup –url -filename

Delete orphan object

Stsadm –o deletesite –url

Detach/Attach Content DB

You can detach/attach the Content DB following two different ways: from SharePoint Central Administration site or from command line using the stsadm command.

Central Administration site

Open the Administrator site and go to Central Administration > Application Management > Content Databases > Manage Content Databases. Ensure the correct site is selected in Web Application and click on Database Name to open the properties page. At the bottom of the page select Remove Content Database option and click OK.

Now the database is not associated with the farm but it has not been deleted from the database server.

To attach it again, go to Central Administration > Application Management > Content Databases > Manage Content Databases, click on Add a content database button, fill the form with the correct information (database server, database name, etc.) and click Ok.

Using STSADM command

To detach the Content database execute the following command:

Stsadm –o deletecontentdb –url -databasename

To attach it again execute this one:

Stsadm –o addcontentdb –url -databasename

Restore the site

Stsadm –o restore –url -filename

Now your farm is free of orphan objects (surface and deep ones) and you can proceed with the farm upgrade.

Please, review the third part (and last one) of this post to take into account some considerations related to Orphan objects in MOSS.