Scenario: I was not able to provision a new project web access instance on a Project Server 2010 / SharePoint 2010 environment. Further troubleshooting this problem I realized that no new site collections could be created on the environment under the specific problematic web application only. Below is the error from ULS:
Failed to provision site [project web access instance name] with error: System.NullReferenceException: 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)
Solution:
This is a case of at least 1 orphaned database associated to the problematic web application.
1. Run below powershell command to get GUID of web application.
get-spwebapplication | ? {$_.displayname -eq "Problematic Web Application Name"} | fl
2. Run this SQL query against configuration database.
select ID, Name, CAST (properties as xml) from Objects where ID = 'GUID of webapp from Step 1'
3. The results returned from step 2 will have 1 column with the properties as xml. Click on the results and it should open a new window in SQL Mgmt studio with xml output.
4. Search for the word "m_Databases" in the xml output.
5. Carefully read through the tag that stores the above searched word. You should be able to see fld tags. Find out a tag which has fld tag with value null. This is the orphan database. Its GUID is the xml line above where you found null.
6. Cross verify all databases currently associated with the web application and make sure this database is not getting used.
7. Run below powershell script,
$webapp.contentdatabases.delete(‘ID-of-bad-content-db’)
This script will throw an object reference error. Do not worry, it works its magic under the hood.
Try to create a new site collection now and it should work.
Summary: The above problem occurs because if there one entry which has fld value set to null SharePoint thinks that that database is the most suitable one to create new site collections in. But since it is an orphaned database, it is unable to do so and reports the above error in ULS which is repeated every time a new site collection creation request is initiated with through SP user interface or Project Server interface.
