In one of the projects I’m currently involved, we’re in the process of upgrading from SharePoint 2010 to SharePoint 2013. One of the problems we faced were the fact that we had some orphaned content databases in our production environments, but the problem didn’t surface in SharePoint 2010 but was given light in 2013. So this short post is talking about how I fixed those issues, which was a bit of a pain to be honest.

In the environments we’re working, we’ve set up a scheduled upgrade that takes place once every week on a schedule. The reason for this is to re-iterate the upgrade process as many times we can, with production data, before the actual upgrade which will take place later down the road when all bugs, code tweaks/customizations and other random problems have been taken care of. One of the problems that surfaced recently was that we couldn’t create any new Site Collections, where the ULS spit out the unfortunate message:

Application error when access /_admin/createsite.aspx, Error=Object reference not set to an instance of an object. at Microsoft.SharePoint.Administration.SPContentDatabaseCollection.FindBestContentDatabaseForSiteCreation(IEnumerable`1 contentDatabases, Guid siteIdToAvoid, Guid webIdToAvoid, SPContentDatabase database, SPContentDatabase databaseTheSiteWillBeDeletedFrom)

While it took some time to boil down the nuts of what was going on, here’s the details in case you end up with the same issues.

Cannot create new Site Collections

So the problem we faced of not being able to create new Site Collections surfaced itself in the ULS logs, stating this message:

Application error when access /admin/createsite.aspx, Error=Object reference not set to an instance of an object.

at Microsoft.SharePoint.Administration.SPContentDatabaseCollection.FindBestContentDatabaseForSiteCreation(IEnumerable`1 contentDatabases, Guid siteIdToAvoid, Guid webIdToAvoid, SPContentDatabase database, SPContentDatabase databaseTheSiteWillBeDeletedFrom)

at Microsoft.SharePoint.Administration.SPContentDatabaseCollection.FindBestContentDatabaseForSiteCreation(SPSiteCreationParameters siteCreationParameters, Guid siteIdToAvoid, Guid webIdToAvoid, SPContentDatabase database, SPContentDatabase databaseTheSiteWillBeDeletedFrom)

at Microsoft.SharePoint.Administration.SPContentDatabaseCollection.FindBestContentDatabaseForSiteCreation(SPSiteCreationParameters siteCreationParameters)

at Microsoft.SharePoint.Administration.SPSiteCollection.Add(SPContentDatabase database, SPSiteSubscription siteSubscription, String siteUrl, String title, String description, UInt32 nLCID, Int32 compatibilityLevel, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String secondaryContactLogin, String secondaryContactName, String secondaryContactEmail, String quotaTemplate, String sscRootWebUrl, Boolean useHostHeaderAsSiteName, Boolean overrideCompatibilityRestriction)

at
Microsoft.SharePoint.Administration.SPSiteCollection.Add(SPSiteSubscription siteSubscription, String siteUrl, String title, String description, UInt32 nLCID, Int32 compatibilityLevel, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String secondaryContactLogin, String secondaryContactName, String secondaryContactEmail, Boolean useHostHeaderAsSiteName)

at Microsoft.SharePoint.ApplicationPages.CreateSitePage.BtnCreateSiteClick(Object sender, EventArgs e)

at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Given some reflector magic and investigations I found out that this specific method causing the problem was looking for the best Content Database to put the new Site Collection in. While it was trying to do this, it obviously want to balance the Site Collections in a way that means they’re evenly distributed over the Content Databases.

The reason for why we got this error message is due to invalid references in our Config database pointing to Content Databases that no longer exist, for whatever reason. The result of this is that the method tried to create the new Site Collection into a Content Database that doesn’t really exist, even though SharePoint thought it existed.

Steps to find and kill the broken/invalid references to the non-existent content databases

After some SQL magic, finding out the null-references were rather easy. Following these steps allowed me to figure out the details of the broken databases:

Step 1: Get the Web Application ID

Either use SharePoint Manager or simply a quick PowerShell statement to quickly figure out the GUID of your Web Application where the problem is persisted:

$wa = Get-SPWebApplication http://awesome.intranet.com $wa.ID

You should note/save this ID for reference in the next steps.

Step 2: Query your Config database for the appropriate information

Save this ID, head on over to your SQL server and run this command (replace GUID with your ID from Web App)

USE SP13_Config SELECT ID, CAST(Properties as XML) AS 'Properties' FROM Objects WHERE ID = 'GUID' -- GUID of the Web Application

As you can see when using the CAST(Properties as XML) bit of the query, you can get a clickable link in the results window given you an awesome overview of the XML represented. Thanks to a SQL friend of mine for pointing that out, saved the day :-)

Here’s what the results looks like (1 row):

Step 3: Investigate the returned results (XML) and find your null-values

Click the XML link and find this section containing the Microsoft.SharePoint.Administration.SPContentDatabaseCollection and see if you find any place where the fld value is null, something like this:

As you can see, most of the databases in our environment has a sFld and a fld xml node where the GUID of the database are stored. However in some cases (in 2 places in our environment!) you may find that it says null instead. That is essentially your invalid reference pointing to nothing at all. So SharePoint tries to create the Site Collection in the Content Database with the null-fld.

As with previous steps, make a note of the GUID from your broken database references.

Step 4: Delete the database(s) using PowerShell

The best way we found to delete these databases were by using PowerShell. At first I didn’t think it actually worked, but after re-running the SQL query after running the PowerShell command it occurred to me that the command had actually removed the invalid reference. The reason for why I didn’t think it worked is because PowerShell is throwing some errors on the screen for you, but it looks as if it’s actually working the right magic under the hood for us – thus leaving us with an intact and working farm again.

So, make sure you’ve got the ID’s of your broken databases and first and foremost make sure that you haven’t copied the incorrect GUID (!) – what I did was simply query my Web Application and filtered the query to give me the ID and Names of all Content Databases so I could make sure that I didn’t delete an actual Content Database by mistake.

Verification command:

$wa.ContentDatabases | ft ID, Name

After running this command we got a list of databases where we could just make sure that the GUID’s we’ve coped didn’t actually represent any of our real databases that were intact:

Great, now that I’m sure the ID of the databases I copied isn’t the ID of a production DB which I know is intact, but represents my broken ones, I can execute the delete-command on those buggers!

In order to do that, I simply ran this PowerShell command:

$wa.ContentDatabases.Delete("GUID")

The results of this were as follows, causing a lot of nice error messages.. However, the magic under the hood still worked:

Step 5: Verify by running the SQL query again

So the PowerShell throws an error message stating that “Object reference not set to an instance of an object.”, however under the hood the magic has been applied properly and in my Config-database the values that were incorrect are now deleted as can be verified if we re-run the SQL query:

Summary

Well, I’ve learnt a lot this week about the Config database and playing around with the GUIDs within. The scary part was that these errors didn’t surface in SharePoint 2010, but they did in 2013 once we upgraded. Another good reason to get a good iterative upgrade-routine in place before an actual upgrade is attempted.

Speaking about iterative upgrade processes I might discuss that in a future post, namely how we commence our upgrades every week without lifting a finger (almost) :-)

Enjoy.