In our University environment we decided to pre-configure MySites for our 18,000+ users across 30 different content databases using a Powershell script. While running a file to update composed looks, our log file indicated that 13 MySites weren’t created. These users all had a site collection under our MySite host web application, however, clicking on the name did not display any information (no db name). It was also impossible to delete these site collections, nor could we create another MySite for these users because SharePoint considered these sites to have already been created.
At this point I thought we were dealing with some corrupted MySites. Following Joel Oleson’s excellent blog post on removing corrupted SharePoint Sites, I used STSADM -o EnumAllWebs on each of our 30 databases looking for InSiteMap=”False”, which would identify the site collection GUID so it could be deleted via Powershell.
Unfortunately I didn’t find any.
After several hours on the phone with a Microsoft tech, I found out that I was dealing with orphaned sites, specifically Configuration Database Orphaned Sites (versus Content Database Orphaned Sites, which was determined when I couldn’t find the sites in any of our content databases).
What is an Orphaned Site?
According to this 2005 blog post by Maurice Prather, “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.”
Having Configuration Database Orphaned Sites turned out to be the easiest to clean up. The fix is to simply go to CA –> Application Management –> Manage content databases and remove the content database, then add it back in. This cleans up the entries in the Sites table from the Configuration Database. This is because the Column “DatabaseID” in this table refers to the Content Database that the site belongs to. When you remove the Content Database, we clean up all entries related to that databaseID from the Sites table in the Configuration Database. After this was done, our users were able to successfully create their own MySite.
The Nitty Gritty
First you need to find the name of your farm configuration database: CA –> System Settings –> Manage servers in the farm.
Open your SQL Server using SQL Server Management Studio. Expand the database name and the Tables folder.
Right-click dbo.SiteMap and select “Select top 1000 rows”
Edit the query to:
SELECT * FROM [<name of farm configuration db>].[dbo].[SiteMap] where path like '/<sitepath>/<username>'
Execute query.
In the results pane, copy the DatabaseID (ie: C8963B2D-E088-4D71-93F9-7131B2E50A10)
Right-click dbo.Objects and select “Select top 1000 rows”
Edit the query to:
SELECT * FROM [<name of farm configuration db>].[dbo].[Objects] where id like '<DatabaseID>'
Change the id to the DatabaseID from the previous step.
Execute query.
You’ll find the database name in the results table.
Use SQL Server Management Studio to make a backup of the database.
Go to CA –> Application Management –> Manage content databases. Make sure you are in the correct web application.
Click on the appropriate db.
Put a check in the box next to “Remove Content Database” and click OK in the warning message. Click OK again.
Refresh the page. At the top of the Content Databases page, click on “Add a content database.”
Change the “Database Server” to the appropriate name.
Change the database name to the same name as before. Click OK.
Now to check to see if the site collection is still in the database
Go to CA -> Application Management –> View all site collections
Make sure your MySites host is selected as the Web Application.
Search for user.
User should now be missing from the MySites web application.
Success! Now the user can create their MySite!