Thursday, November 27, 2014

How to Delete a Service Application Database that is Not Responding

Posted in [SharePoint], [Technical] By Doug Allen @ 1/29/2013

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. 

, , , , , , ,

Comments

Got something to say? Join the discussion »
    Posted by skrasher on 9/30/2013
    Thanks! I had an orphaned State Service DB that was 'Not Responding' when I checked in Central Admin. The service app was deleted, and the DB was not there in SQL, but it remained there and caused errors when upgrading (Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException). Removing it this way fixed it and things are clean again.
    Posted by Goran Husman on 5/21/2013
    Excellent article - Exactly what I was looking for!
    Thanks!
    /Goran
    Office 365 MVP
    Posted by Dimitri C. on 5/16/2013
    This solved my issue, thanks !

leave a reply

 [Quick Submit with Ctrl+Enter]

Remember my details
Notify me of followup comments via e-mail

C5_Insight

About C5 Insight

We are a Microsoft Gold Certified partner focusing on SharePoint, Microsoft Dynamics CRM and Salesforce.com.  Learn more about us by visiting our website.

Search

We Wrote the Bible on Microsoft SharePoint and Dynamics CRMBook-Microsoft Dynamics CRM 2011 and SharePoint 2010 Bible

Tags

Maximize

Recent Comments

Maximize

Blog Roll

Maximize

Disclaimer

The information herein may be used solely at your own risk.  No warranty is made by the author or by C5 Insight, Inc.

The opinons expressed herein are those of the individual authors and do not necessarily represent C5 Insight, Inc in any way.

Copyright 2011-2013 by C5 Insight