Sharepoint Orphans Explained
What are Orphans in SharePoint?
Orphans have been coined the name for objects in a SharePoint schema that live without a parent or child relationship in the database. In effect these database inconsistencies can be created due to many different reasons. They cause confusion, helpdesk tickets and can cause your upgrade from wss2.0 / sps2003 to MOSS 2007 / wss3.0 to fail. We will explore some of the ways these can be created, how to locate them, methods for resolving orphans and a few key things you can do to help avoid them in the first place.
In order to really understand orphans we need to understand a high level overview of how SharePoint configuration databases and SharePoint content databases interact.
Figure 1.1 Configuration Database Content Database relationships
Orphans come from a host of different reasons. Unfinished create and delete transactions being the main source. In SPS2003 if you were to create a new SharePoint site and click back, or close the window in mid creation it would essentially stop where you clicked back. More often than not it would not roll back the transaction or clean it in any shape or form, a very sloppy end to a standard task in SharePoint. This in turn creates a Surface orphan. For the sake of this entry any site level orphan (content>config/config>content) we will refer to as Surface orphans.
Figure 1.2 Surface Orphans
Another source of orphan creation is actually caused from farm maintenance. Any farm running a mysitehost should proceed with caution while performing database maintenance. MySitehosts are root portals that automatically provision a new site collection to a user when he/she visits the portal for the first time. This can be very handy for on-boarding new employees etc. This can be a huge nightmare for the operations team which supports the mysitehost. In SharePoint if you move a database from 1 SQL server to another it requires you to detach the database from the Farm Admin GUI and reattach using the new SQL instance name. The huge problem with doing this while the farm is accessible by its users is the fact that once you remove that mysitehost database from the admin GUI that mysite host no longer has entries for any site in the content database you are migrating. Thus any user that visits your mysite host that has a site in the content database being moved will automatically be provisioned a new site. Once you reattach that database in the Admin GUI it will repopulate all of its configuration database entries. However if a site had been created with a same URL while that database was not attached, it will skip that site collection as one already exists. Thus you have another surface orphan. This causes a great deal of confusion from a customer standpoint.
Let’s step back and view this from the customer/user.
Monday – I visit http://mysitehost and my site is created, I add new documents, NEO information etc…. Throughout the week I update my Documents all is well.
Friday 7pm-11pm - the IT Team performs maintenance on mysitehost_database_1 they are migrating it to another SQL server. It just so happens that my site lives in mysitehost_database_1, this migration consists of the IT Team detaching the database from the FARM while it is in transit.
Friday 8:30pm – I the unknowing user to this maintenance visit http://mysitehost, since the content database which contains my site has been detached from the farm it does not know of its existence. It in turns creates a brand new site for me at http://mysitehost
I notice all of my documents I have been working on all week are missing; I file a restore request to helpdesk to retrieve my documents.
Are the documents really gone? Nope, they are in that database being migrated. However SharePoint now has a new entry in that darn configuration database for this same site. When the IT Team at 11:00pm reattaches mysitehost_database_1 to the farm it will not write an entry in the configuration database to point to this week old site. It already exists right!? We just created a new one 2.5 hours ago… Thus a new surface orphan.
Below is a 3 step story board showing this visually.
Phase 1 Before Database Migration
Phase 2 During Migration
Phase 3 End result.
Now imagine that your company of 5000 employee’s all have their mysite host site living on 2 databases. I think you start to get the picture, if even 100 users visit their site while the migration is underway of 1 of those databases that’s 100 helpdesk tickets and 100 new orphans in your farm.
Alas, there is a bit of light throughout this dark tunnel with these different types or orphans. You can reverse the process. It is neither pretty, nor fun for the IT Team J but it is possible. And most importantly it is preventable for the most part.
How to find Surface Orphans
So you have orphans, before we can fix them we need to find them!
You can only detect orphans through SQL, there is no real out of the box way to detect them from the web UI. That’s ok SQL is fun anyway J Into SQL we go. To use the following script all you require is an open Query window with read access to all databases within your farm. The following script assumes you run all of your databases on the same SQL instance. This script is an example for finding orphans in MOSS 2007/WSS3.0
PS. I am not a SQL Guru
ps#2 Use these SQL scripts at your own risk, even querying the database is not supported!
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 = 'spskills_config_db'
/** 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
Running this script will give you ConfigurationDatabase, ContentDatabase, SitePath, SiteID and Potential Type of orphan(contentDB or ConfigDB).
Wonderful now we have a list of items to clean!
How to Clean Orphans from your environment.
(Be smart have backups prior whenever you are performing maintenance on your farm, use suggestions at your own risk.)
Configuration Orphans: These are the orphans that reside in your configuration database but have no child counterpart (contentDB entry). Cleaning these are the easiest of all the orphans. Simply detach the content database from your farm that was included in the result set and reattach it. This will refresh the sitelist that is tied to that content database and will remove the stale entry.
Content Database Orphans: This is where it gets tricky.
There are 2 essential types of content database orphans.
Type 1 Scenario(reactive maintenance): Your site that has the content you need is not mapped to the configuration database but resides in a content database that is connected to the farm, additionally a blank new site is mapped to the configuration database. This would hold true to the second scenario explained above. To resolve this simply backup the site that is accessible, then delete it. Once done detach and reattach the database that contains the real site. This will remap the site to the configuration database. You now have access to a once orphaned site and all of your content is restored.
Type 2 Scenario(Planned Maintenance): The correct site is mapped to the configuration database, however you have stale orphans in other databases. Simply backup your production site and delete it. Once that completes detach and attach the database that contains the stale orphan, this will in effect map the orphan to the configuration database and render it accessible. You can then delete it using STSADM. Perform these steps until you have cleaned all orphans. Once all orphans are clean you can then restore your production site back into the farm. Viola, orphan free :)
How to help prevent orphans.
- User Education
Remind users that creating and deleting a site can be a sometimes several minute process. Regardless of how long it takes let the application finish what it is doing. If it times out then try again but never click back, stop, or close the window when performing these types of administration tasks. Network Latency, Web front end performance and SQL backend performance can all attribute to a slow create or delete statement. Be patient :).
- Operations Maintenance
When performing maintenance to a database where you are taking it offline for any reason consider it a good practice that if possible takes the entire portal offline. Even though you may be only working with a small fraction of the farm you could potentially be creating a lot more problems than you realize. If you are not able to bring down the entire portal a suggestion to alleviate the potential for orphans it to leave the database attached to the farm UI while it is in transit / detached from SQL. The user will then get a "Cannot connect to database error." Rather than being able to create a potential orphan. Then as your last step for the migration perform the detach/attach in the admin UI very quickly limiting the possibility for orphan creation.
Hope this helps some of the confusion out in the world on orphans!
Cory
