Friday, December 23, 2011

SP2010 - Cannot create new site collections


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.

10 comments:

  1. Brilliant article. Just resolved an issue on a test system I have.
    Thanks

    ReplyDelete
  2. Hello Steve

    thanks for the post, i`m facing the same problem.

    After running the select command from step 2 i received this error in the SQL query :


    Conversion failed when converting from a character string to uniqueidentifier.

    any idea ?

    ReplyDelete
  3. Worked perfectly. For the last PowerShell script it should be noted that you have to set $webapp to the webapp you are working with: $webapp = get-spwebapplication http://problemwebapp

    ReplyDelete
  4. I got another error:
    PS H:\> $web.contentdatabases.Delete('c10474e3-d937-4e7a-aa4d-b075238f83cd')
    Exception calling "Delete" with "1" argument(s): "Object reference not set to an instance of an object."
    At line:1 char:29
    + $web.contentdatabases.Delete <<<< ('c10474e3-d937-4e7a-aa4d-b075238f83cd')
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    I have also tried to dismount this content database, same error with message: Object reference not set to a
    n instance of an object.

    Please advise.

    ReplyDelete
  5. Oh, It's work. Sorry, I was not read in details: "This script will throw an object reference error. Do not worry, it works its magic under the hood."

    Thanks so much, great post!.. :)

    ReplyDelete
  6. This is still golden! Saved the day. For step 7 ran this:
    (get-SPWebApplication http://your.webapp.name.here).ContentDatabases.delete('The GUID from step 5')

    ReplyDelete
  7. I have faced the same error while creating a site collection from central admin.The database disk space was full from the log files.After clearing out these log files, I was able to create the site collections without any issue.

    ReplyDelete