Monday, 15 September 2014

Deleting Orphaned SharePoint Databases

When you delete a Service Application, but not the data, databases are left behind. Should you want to clean up references to these databases later, here's how.

Use the Get-SPDatabase cmdlet to list all of the databases the Farm knows about. The output of this command is verbose, so pipe it to Format-List and select a subset of the properties.

Get-SPDatabase | FT Name,Exists


Notice how the IsAttachedToFarm and ExistsInFarm properties don't report true or false for most of the service applications?

It's not a problem! You can use the Exists property (on each database) to filter that list to just databases that SharePoint "thinks" don't exist.

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false} | %{Write-Host "DB"$_.Name"does not exist." -f red}

Or

$dbs | %{Write-Host "Database"$_.Name;if($_.Exists){Write-Host "DB Exists." -f Green}else{Write-Host "DB does not exist." -f red}}



To remove these database references, call the Delete() method, and then the Unprovision() method, on each database.

For Example:

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false}  | %{Write-Host "DB"$_.Name"does not exist. Deleting and cleaning up references." -f red; $_.Delete();$_.Unprovision()}