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!!

How do delete corrupted site collections in SharePoint 2010 ?

Scenario :  Move-SPSite command failed during moving an SharePoint site collection(of size 50 GB) from content database A to Content Database B within same SQL instance.

Description : While moving the site from content database A to Content Database B, if it run for sometime and finally failed due to any reason, then again if you would like to move the site to same content database B, then it may fail. Because content database B is having the entries of the site in it's tables which inserted during the last move-spsite execution.

However if you go to central admin and verify the sites count in the content database B, you will not find any difference, but if you open the Database B in SQL Management Studio and verify the tables, you will find the entries of this site collection. Also if you verify the content database size, it must be increased due to corrupted site collection.

Note: Your original site is safe and accessible from database ''A''.

SQL Query to find the site collection url from content database B

Select FullUrl from Allsites

Problem 1: Content database unnecessarily occupy the space for the corrupted site collection.
Problem 2: Unable to move the same site to content database B.

Resolution:

Solution 1: Verify if the Gradual Site delete timer job helps !

Solution 2: Execute below powershell command to delete the corrupted site from content database B.

$objSite = Get-SPSite "http://test.sam.com/sites/testsite"

$objSiteID = $objSite.Id // although this ID will display from Content Database A, but this ID is also same for the site in content database B

$objContentDB = Get-SPContentDatabase "B"
$objContentDB.ForceDeleteSite($objSiteID, $false, $false) // It will take sometime, wait until it is finished.

Testing:

Now you can verify the same SQL query if it's returning the corrupted site collection(/sites/testsite)

Select FullUrl from Allsites

Also you can ask the SQL team to sync the database ''B'' whose size should be decreased.

Hope this helps !