How to delete an Orphaned SharePoint database in SharePoint 2010 ?

Note: This needs to be carefully executed after you have tested all other troubleshooting steps.

Scenario: Under View All Site Collections of a web application, you click on the site URLs and it doesn’t show you the details about the site URL, Site Description, Primary Site Collection Administrator etc.

In this case, it could be that the content database was detached from SharePoint and was also requested from SQL to remove it from the SQL instance.

However when you look for site collections it still refers to those deleted site collections from that detached content DB and thus it doesn’t show the details as mentioned above regarding site URL, Site Description, Primary Site Collection Administrator etc. This happens because the presence of that content database is still known to the SP_Config database.

This situation tells me that this DB is now an Orphaned object and needs to be cleaned up. However I cannot simply remove this object from SQL directly because as per Microsoft, anything executed from SQL side in terms of addition/deletion, we would not get any support from them.

So to address this situation without putting our Microsoft support at risk, we can leverage SharePoint PowerShell to achieve it.

The process is simple.

First we try to find this corrupted/orphaned database entry in the SQL SP_Config DB (Objects table)

SELECT * FROM Objects WITH (nolock) where Name = '<your orphaned content database name>'

This should show you an entry. Then you need PowerShell to get this database in an object

$orphanedDB = Get-SPDatabase | where{$_.Name -eq “your orphaned content database name”}

Once you have the object, please verify the object details by executing “$orphanedDB”. It should be like this.

PS C:\Users\sam2015> $orphanedDB = Get-SPDatabase | where{$_.Name -eq “your orphaned content database name”}

PS C:\Users\sam2015> $orphanedDB

Id               : 564d9d82-4f65-4b88-b20b-543ff498e40e7

Name             : “your orphaned content database name”

WebApplication   :

Server           : <<SQL Server Name>>

CurrentSiteCount : 33

So as you see above, the content database is known to the SP_Config DB, however it is not known to any web application

Hence this Orphaned object can be removed as below

$orphanedDB.Delete()

Then you refresh the Central Admin site and you should see all site collections with proper details next to them

Hope this helps!!