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 !