Search Database GUID issue

So in the quest to have no GUIDs I wrote the blog post How to remove the GUIDs from the SharePoint search service application databases to great fan fair. After reviewing my VM and other servers I have performed the steps on I have found the error message below. (Text and image included to help with the search engines getting you here.)

SQL Database 'NewSearch_DB_e28b777e4e664c479879f5a257b932f9' on SQL Server instance 'CowTown' not found. Additional error information from SQL Server is included below.

 

Cannot open database "NewSearch_DB_e28b777e4e664c479879f5a257b932f9" requested by the login. The login failed.

Login failed for user 'CONTOSO\sp_farm'.

(Don't make fun of my server being named CowTown.)

So that seemed kind of scary but made sense because I had deleted that old database. After digging around I found that search seemed to work fine and apparently one of the timer jobs was just trying to check this database once an hour. So first thought I had was "I wonder if SharePoint still thinks that is a database somewhere even though the Search service application isn't using it?"

Well, I know PowerShell to find out the answer to that question. I opened up a SharePoint Management Shell and simply ran:

Get-spdatabase

Which gave me the output below:

Sure enough. The old database is still listed even though Search isn't using it. UGH. (Fifth database from the bottom.)

Another place I saw it that was kind of freaky was Central Administration > Upgrade and Migration > Review database status

Here you can see that it is listed as Not Responding. UGH.

So all of this makes sense. We did delete the database because Search doesn't use it anymore. Now we just need a way to tell the farm to quit trying to reference it. I fought with about 3 different ways to make this work and finally I came up with this one which I believe should be completely supported. J (My other ways may have been a little too direct.)

Getting rid of the error

  1. Open the SharePoint Management Shell
  2. Type get-spdatabase and press enter. This will give the output below.

  3. Find the database in question. You need to then copy the Id for it. You can right click in PowerShell and choose mark.
  4. Type $bad = get-spdatabase <your id> and press enter.

  5. Type $bad and hit enter. This will show you the database you have in the variable. It is your last chance to double check you got the correct database.

  6. Type $bad.Delete() and press enter. No more database.

  7. Type get-spdatabase and press enter. All better.

At this point all of your worries are gone. No more stupid error message. Standard precautions try this in your test world first. You are up a river without a paddle if you do this for the wrong database since it is a pretty violent way to delete the database.

May the force be with you!

Shane

SharePoint Consulting

Comments

# Getting rid of GUIDs in SharePoint 2010 database names

Monday, December 06, 2010 10:24 AM by The SharePoint Farmer's Almanac

It is like your typical battle of good vs. evil. SharePoint 2010 has a secret plot to run the world out

# re: Search Database GUID issue

Monday, December 06, 2010 10:34 AM by Khan

Awesome! deleted the DB now waiting to see if error pops up again!

# re: Search Database GUID issue

Tuesday, December 07, 2010 4:05 PM by Knox Cameron

Good stuff!

While it would be really bad to delete the wrong database, it should still be recoverable I would think, since "delete" in this context really just means drop the connection rather than actually delete the database off SQL.

By the way, the word in the first paragraph should be "fanfare" not "fan fair".

# re: Search Database GUID issue

Wednesday, December 08, 2010 4:06 AM by Bill Morris

Thanks for this trick , It's really working and you have solved my problem so easily. Cheers:)

# Price for SharePoint Apps Increasing; Windows Phone 7 a Work in Progress; IE9 Anti-Tracking Option

Wednesday, December 08, 2010 7:53 AM by SharePoint Daily

Top News Stories 6 Requirements for Writing SharePoint Requirements (CMS Wire) Now that you’ve

# re: Search Database GUID issue

Wednesday, December 08, 2010 7:56 AM by Mike Pasco

Thank you for this, we have had this issue for a while now. In both of the farms we had this happening, the old search admin db had a property of Exists = False. Adding this where clause should help narrow it down:

Get-SPDatabase | where {$_.Type -eq "Microsoft.Office.Server.Search.Administration.SearchAdminDatabase" -and $_.Exists -eq $False}

# re: Search Database GUID issue

Monday, March 28, 2011 2:17 PM by Nava

Excellent information and I was able to follow all the steps but struck at $bad.Delete()

This is the message I am getting

PS C:\Users\adminsp> $bad = Get-SPDatabase b8da1e82fce440b48813ec7607485849

PS C:\Users\adminsp> $bad

PS C:\Users\adminsp> $bad.Delete()

You cannot call a method on a null-valued expression.

At line:1 char:12

+ $bad.Delete <<<< ()

   + CategoryInfo          : InvalidOperation: (Delete:String) [], RuntimeException

   + FullyQualifiedErrorId : InvokeMethodOnNull

Please help

# re: Search Database GUID issue

Monday, March 28, 2011 3:52 PM by Nava

Thanks a lot Shane.

I was using wrong ID. I used this command which you send me

(Get-SPDatabase | select Name, ID)

to get the real ID for the databaes and now I am able to delete.

# re: Search Database GUID issue

Wednesday, March 07, 2012 2:57 PM by Moojjoo

You rock...

#Purpose: Clean up delete Search dB from the server causing eventID 3760 errors on the Central Admin server

if ((Get-PSSnapin -Name Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue) -eq $null )

{

   Add-PSSnapin Microsoft.SharePoint.Powershell

}

get-spdatabase | Format-List -Property Name, Id

#$oldDB = get-spdatabase 0cdf30c6-b5eb-42ca-94f6-c7c1a615bad3

#$oldDB.delete()

# re: Search Database GUID issue

Wednesday, April 25, 2012 12:53 PM by lonuel

$bad.name shows the complete database name.

# re: Search Database GUID issue

Friday, May 11, 2012 7:15 AM by PhY

nice job !

# re: Search Database GUID issue

Wednesday, May 23, 2012 11:35 AM by Eric

When i run $bad.delete(), i get the following  "...., could not be deleted because other objects depend on it."  

The DB was renamed in the web analytics service applications properties.  The old DB name with GUID no longer exists in SQL Management Studio.  The DB does querey the old DB with GUID when i run get-spdatabase | format-table name, id.

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: