Orphaned Sites - Part 1

  • Article

Orphaned Sites - Part 1

There's been a good deal of action around the whole issue of Orphaned sites recently, so I wanted to take some time and provide you the following information in regards to this problem.

  1. What's an Orphaned Site?
  2. What do we know about what causes Orphaned Sites?
  3. How do I determine if I truly have an orphaned site?
  4. What we've done to prevent Orphaned Sites 
  5. What we're doing to cleanup Orphaned Sites
  6. What NOT to do to cleanup Orphaned Sites

DISCLAIMER: This post shows using Query Analyzer to query data in your SharePoint sites. By no means does this mean that you should change any thing in the database. This is simply for "READING" values. And even this should be done during Off-Peak hours.

1. What's an Orphaned 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.

You may notice you have an orphaned site via the following operations:

  1. STSADM -o restore fails to restore the site, even with the -overwrite option when you know the URL exists.
    1. You may receive an error code 0x80040E2F
    2. 0x80040E2F means “Violation of PRIMARY KEY constraint”, this equates to STSADM already finding previous rows in the database for the items it's trying to restore.
    3. STSADM first looks at the Configration and Content Databases to ensure that the site does not already exist, and if so (And you didn't use the -overwrite switch) it would bail out, but if it does not find this information, it begins restoring...Then runs into the problem noted above.
  2. STSADM -o deletesite fails to delete the site 
    1. You may receive and error which states "The site does not exist"
    2. Same thing. No site information in Configuration or Content database in their respective Sites tables. Thus the other remnants of the site cannot be cleaned up.

2. What do we know about what causes Orphaned Sites?

We have only found two identifiable causes for Orphaned Sites.

You cannot restore a Windows SharePoint Services Web site when the number of sites reaches the warning limit
You expect that you can restore a Windows SharePoint Services Web site until the number of sites reaches the maximum limit. The maximum limit is specified in Maximum number of sites that can be created in this database. However, you cannot restore a Web site when the number of sites reaches the warning limit. The warning limit is specified in Number of sites before a warning event is generated. When you try to restore a Web site and the number of sites reaches the warning limit, you receive the following error message:

The content databases in this cluster have exceeded the warning Web site count.

Either change the content database Web site capacity settings or add more content databases

You interrupt an STSADM -o restore operation, or the operation is interrupted by other non human interference :)

3. How do I determine if I truly have an orphaned site?

Consider that you have a theory that your site collection at https://yourserver/sites/coolstuff is orphaned. The quickest way to determine this is as follows:

Use a query tool (Such as SQL Query Analyzer or OSQL) to run the following query on the Configuration Database in your SharePoint farm.

   select * from Sites where UrlPath LIKE '/sites/coolstuff'   

If this returns data, then the Configuration database does have information about your site. Proceed to next step.

Use a query tool (Such as SQL Query Analyzer or OSQL) to run the following query on the Content Database in your SharePoint farm.

select * from Sites where FullUrl LIKE '%/sites/coolstuff'

Notice that this URL string has the mask character at the beginning (Because we store absolute URLs)
If this returns data, there should be sufficient information for a STSADM -o deletesite to work properly to delete your site. i.e., you don't have an Orphaned Site.

If you get results from querying the Configuration Database but not the Content Database then you have a Configuration Database Orphaned Site.
This is the easiest to clean up. Simply visit the Managed Content Databases page in the Windows SharePoint Services UI and remove the content database. Then just re-add it back in.

This cleans up the entries from the Configuration Databases' Sites table. This is because the Column “DatabaseID” in this table refers to the Content Databasethat the site belongs to. When you remove the Content Database from the Virtual Server, we clean up all entries related to that DatabaseID from the Configuration Databases' Sites table.

If you get results from querying the Content Database, but not the Configuration Database, then you have a Content Database Orphaned Site.

This is not so easy to clean up :). See Section 5 :)

4. What we've done to prevent Orphaned Sites 

The good news, is that we've already completed and released work to prevent this problem in the future and these fixes are included in the Windows SharePoint Services SP2 update.

See https://support.microsoft.com/?id=906336 for more information.

5. What we're doing to cleanup Orphaned Sites

We are currently working on a fix (it will probably show up as a new STSADM operation) to clean up these orphaned site entries. We're certainly aware of this issue, and the importance of it. I would say we're probably still a month off from the date of this posting before we'll have the official hotfix ready (Lots of testing)

In the mean time, I'll be detailing how to clean up the orphaned site issue in my next post titled "Orphaned Sites - Part 2".

6. What NOT to do to cleanup Orphaned Sites.

Whatever you do, do NOT PERFORM DIRECT EDITS TO THE SHAREPOINT DATABASE. 

  • Do NOT manually update records
  • Do NOT manually insert records
  • Do NOT manually delete records
  • Do NOT MODIFY SHAREPOINT DATA!!!

A perfect example of this, is when I saw the numerous posts where folks were just doing direct edits to the UserInfo table when the users account was migrated between domains. They'd go in and just update the SID directly for the users record in the UserInfo table. They said "Hey this is all that's needed, it works great!!!"

Well, surprise guys...You just created a security hole if your using search. Why? Because we store the ACL for the webs, etc in the indexes to do proper security trimming :) If you changed the SID information directly, then we CAN'T do proper security trimming on any indexes that are not updated yet. The indexes will never be updated properly until you go into the SharePoint UI and change the security settings on the web because the web was not updated to a dirty state.

Of course our Developers knew this when they created the MigrateUser APIs to mark the web as "Dirty" so that the indexer will re-crawl the security information for proper security.

So folks, please be patient.  

- Keith Richie