In my previous post "Orphaned Sites - Part 1" I detailed the following points:

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
   

In this post, I'll detail the steps needed to cleanup after an orphaned site problem

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.

Cleaning Configuration Database Orphaned Sites

This is definitely the easiest type of orphan to clean up as all you need to perform is basically removing and re-adding the Content Database that the Configuration Database an entry for.  When the content database is removed from the virtual servers list of content databases, clean up logic is performed to remove the entries from the Configuration database.  You then simply re-add the content database back in, and the sites from the content database are re-added to the configuration database.

Here are the steps:

1. Use a query tool (Such as SQL Query Analyzer or OSQL) to run the following query on the Configuration Database in your SharePoint farm to get the information about the Orphaned site that we have.
   
      SELECT * FROM Sites WHERE UrlPath LIKE '/sites/coolstuff'  
   
   In the results from this query, you'll notice the DatabaseId column. This column contains the GUID of the content database that is "supposed" to have the site.

2. Query the "Databases" table to get the name of the Content Database
   
      SELECT * FROM Databases WHERE DatabaseID = '{FB038985-874B-4C10-BCBD-DED638B30B71}'  
   
   In the results from this query, get the name of the database from the Name column.  This is the name of the content database you want to remove and re-add from the virtual servers list of content databases

3. You could of course use a join query to get the name for you, such as the following:
   
      SELECT d.Name FROM Databases d, Sites s
      WHERE d.DatabaseId = s.DatabaseId AND s.UrlPath LIKE '/sites/coolstuff'

4. Once you have the name of the database to remove, then proceed with the following:

   1. Visit the Windows SharePoint Services Central Administration page
   2. Select Configure Virtual Server Settings
   3. Select the appropriate virtual server
   4. Select Manage Content Databases
   5. Select the database identified by Steps 1-3.
   6. Make note of the Database Server, Number of sites.... and Maximum number of sites... settings
   7. Choose the Remove content database check box and choose ok.
   8. Choose Add a content database from the Manage Content Databases page
   9. Populate the appropriate information back in that was noted from step 6.

Your Configuration Database Orphan is now gone.

Cleaning Content Database Orphaned Sites

This cleanup method is definitely not the most trivial, and extreme care should be taken before executing.   In other words, be sure to have a complete backup of everything before performing these steps.

In order to have a clean Content Database without any orphaned entries, you basically have to move all the "Non-Orphan" sites into a new content database, then remove and delete content database with the remaining orphans.  In other words, you have to re-partition the good sites into different databases.

In order to repartition Site Collections in and out of content databases you have to use the following steps:

1. If you have not done so already:
   1. Create a new content database to place the backed up site into.
2. STSADM –o backup (to backup the site collection to disk)
3. STSADM –o deletesite (To delete the site from the current content database)
4. Visit the Content Database management page from the Windows SharePoint Services Central Admin pages.
   1. Select the First content database and either:

                                          i.    Set it’s status to Offline or

                                         ii.    Set it’s max-sites and warning-sites counts to that equal to the number of current sites in the content database (Be sure to have a notebook handy to write down all of the original values so that you can re-set them in a later step.)

2. Repeat step 4.a for each content database EXCEPT the one you are restoring to (This prevents the WSS Logic from load balancing the existing content databases, to determine which content database to restore the site in)
4. STSADM –o restore (restore the site into the new targeted database)
5. Visit the Content Database management page from the Windows SharePoint Services Central Admin pages.
   1. Select the First content database and either:

                                          i.    Set it’s status back to Online (If it was online to begin with) or

                                         ii.    Set it’s max-sites and warning-sites counts to their previous values (refer to the notes taken in Step 4.a.ii)

2. Repeat step 6.a for each content database
6. Repeat steps 2-6 (unless you need to create more content databases, in which case you restart at step 1). 

As you can see this is a very labor intensive and time consuming process.  If you had to do this for many sites, although you could be smart, and handle a batch of sites steps 2, 3 and 5, it becomes impractical to do all of this manually.

As an aid to doing just this task, I wrote SPSiteManager which is located in the SharePoint Utility Suite located here:

http://www.microsoft.com/sharepoint/downloads/components/detail.asp?a=724

This is of course an unsupported tool by PSS, but I'm am always open to taking direct email to assist with the tool.

Give it a look-see, and remember..Before doing anything so en-masse, be sure to take a complete backup of your environment.

And of course, remember we are working on a STSADM extension to allow you to clean up orphaned sites, and WSS SP2 prevents them in the future.

- Keith Richie