Issue : 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.BtnCreateSite_Click(Object sender, EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBefore
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
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
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)
SELECT ID, CAST(Properties as XML) AS 'Properties'
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.
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 and a xml node where the GUID of the database are stored.. 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.
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:
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:
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.