Orphan sites are of two types, Content DB and Config DB orphan sites.
Content DB orphan sites have entries of a site collection in content DB but no entries are found in Config Database siteMap table.
Config DB orphan sites have entries of site collection in sitemap table, but no entries for the site exists in content DB. This type of orphan’s are more problematic and can even cause your search crawls to fails.
· Identification of orphan sites by running SQL query
Run preupgrade check on your farm which will generate report with a list of orphan sites. Generally this reports only on content DB orphan sites and not on Config DB orphan sites.
Alternatively you can use the following query to identify all orphan sites in a farm
Use TEMPDB
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)
select @configdb = 'YOUR_config_db_HERE'
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
Drop table orphanlist
· Identification of orphan site type
SQL query output should be pulled into excel and then based on "type" information column determine if the orphan site is a Config DB and Content DB type.
· Steps to clean up content DB orphan sites
Run stsadm deletesite to clean up Content DB orphan site.
Note – Take back up of the content DB’s before deleting the orphan sites.
stsadm -o deletesite -databaseserver "instanceName" -databasename "[ContentDBName]" -siteid "[Site ID]"
· Steps to clean up Config DB orphan site
a. Identify the content DB for the orphan site
b. Make sure that search crawls are not running. Also make sure that user profile import and audience compilation is not running.
c. Detach the content database from sharepoint central admin
d. Reattach the content database using “stsadm addcontentDB”
· Rerun the preupgradeCheck to confirm that orphan sites have been cleaned up.
