“Object reference not set to an instance of an object” Error on Creating Site Collection

Problem: On creating a new Site Collection in SharePoint 2016, I got this error message “Object reference not set to an instance of an object”!

On Checking the ULS logs, it revealed detailed errors as:
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)     at Microsoft.SharePoint.Administration.SPContentDatabaseCollection.FindBestContentDatabaseForSiteCreation(SPSiteCreationParameters siteCreationParameters, Guid siteIdToAvoid, Guid webIdToAvoid, SPContentDatabase database, SPContentDatabase databaseTheSiteWillBeDeletedFrom) 

Root cause:

This is due to a broken content database associated with the particular web application.

Solution:

Remove the invalid content database association by following the below steps.

Step 1: Get the Web Application GUID

Use the below PowerShell script to get the ID of the web application in question.

(Get-SPWebApplication "https://intranet.crescent.com/").ID

Step 2:  Query the SharePoint Config database with the web application GUID:

Login to your SQL Server, Open SSMS, Run this select query against the farm’s configuration database.

SELECT ID, Name, CAST (properties as xml) AS Result 
FROM [Objects]
WHERE ID = '0da122ac-8d9d-4bc1-aa1f-93df85bced3c'

Click on the “Result” column value and search for “m_Databases” and find the database GUID with “null” value.

Step 3: Delete the broken database link:

Now, delete the broken database link using PowerShell.

$WebApp = Get-SPWebApplication "https://intranet.crescent.com/"

$WebApp.ContentDatabases.delete("26449389-6c7e-4c0b-8a89-3ee35c991b35")

This would give: Exception calling “Delete” with “1” argument(s): “Object reference not set to an instance of an object.” But don’t worry.

This error also occurs when your database server runs out of disk space!