powershell_iconOn a recent project, I hit an issue with databases that was interesting.  We were restoring a lot of databases over to a development environment from production, as well as the managed metadata database.  I had gone through the whole deal, backed up the database in the old server, restored it to the development SQL server, etc.  There was an issue with the Managed Metadata service that required to have service application re-created.  This lead to a situation where the service application database was unprovisioned, but not deleted. 

What first caught my attention was the errors in the ULS log.  I was getting a SQL “login failed” for that database which I thought was strange as I thought the database had been deleted when the old service application was deleted. This wasn’t the case.  To see the status of all the databases, go in Central Administration, click Upgrade and Migration –> Review database status.

SharePoint database not responding

Normally you see all the databases as no action required, but our database shows Not Responding.  What does that mean?  If you remember, we got in this scenario because when we deleted the service application, it didn’t fully remove the database. It unprovisioned the database, but failed to remove the entry for the database from the configuration database, which leaves the database as Not Responding. So all we need to do at this point is delete the entry from the config database. 

So how do we get rid of the database?  In our example, we’re dealing with a service application database.  Fire up our trusty friend PowerShell.  The following will show all service application databases:

Get-SPDatabase | where {$_.Type -notcontains "Content Database" -and `
$_.Type -notcontains "Configuration Database"} | sort Type | format-table –autosize

From the output, get the GUID of the our problematic database.  Then run:

$db2delete = Get-SPDatabase “GUID”
$db2delete

This just verifies we have the object correctly.  Once we verify this, we can get rid of it.  Before we delete it, let’s check it’s status:

$db2delete.status

A normal database will show Online.  The status property on a database object uses an SPObjectStatus value.  You can see all of the available statuses, with the exception noted when in the context of the SPContentDatabase object.  Now let’s delete it.  Run the following:

$db2delete.Delete()

Done!  Note, this will not delete the actual database in SQL, just removes the entry in the Config DB.