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

29 thoughts on “Search Database GUID issue”

  1. 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”.

  2. 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}

  3. Shane,

    Nice article.. I tried the way you mentioned in the article, I was not able to delte, below is the resul I got, pleae check it and let me know

    Thankyou
    Neel

    PS C:\Users\spdeveladmin> $bad=get-spdatabase fc21e5f3-622d-4f24-a934-5d0eaa4cb5
    d3
    PS C:\Users\spdeveladmin> $bad

    Name Id Type
    —- — —-
    Test Reporting fc21e5f3-622d-4f24-a934-5d0eaa4cb5d3 Microsoft.Offi…

    PS C:\Users\spdeveladmin> $bad.delete

    MemberType : Method
    OverloadDefinitions : {System.Void Delete()}
    TypeNameOfValue : System.Management.Automation.PSMethod
    Value : System.Void Delete()
    Name : Delete
    IsInstance : True

  4. Shane.. I was not able to delete, below is the result my email neelbh@yahoo.com

    PS C:\Users\spdeveladmin> $bad=get-spdatabase fc21e5f3-622d-4f24-a934-5d0eaa4cb5
    d3
    PS C:\Users\spdeveladmin> $bad

    Name Id Type
    —- — —-
    Test Reporting fc21e5f3-622d-4f24-a934-5d0eaa4cb5d3 Microsoft.Offi…

    PS C:\Users\spdeveladmin> $bad.delete

    MemberType : Method
    OverloadDefinitions : {System.Void Delete()}
    TypeNameOfValue : System.Management.Automation.PSMethod
    Value : System.Void Delete()
    Name : Delete
    IsInstance : True

  5. I had trouble when running $bad.Delete(), I got an “Exception calling “Delete with “0” arguments.” I’ve found that by using central admin and going to Upgrade and Migration then to Mange Databases Upgrade Status and clicking on the offending database, I get the option (near the bottom) to “Remove content database.” This has gotten rid of all of these errors for me.

  6. I stumbled upon this post while trying to troubleshoot 3760 errors. We have yet to figure out why we got this error on ALL our SharePoint databases. but after a reboot, SQL Server put all the databases into Recovery Mode. After about 2 hours of recovering each database, I noticed that the 3760 errors started to cease as each database completed their recovery.

    Long Story Short: I’m left with this lingering SQL Database ‘Search_DB_0a7492f59488424da7f81072dda57244′ on SQL Server instance ‘NameOfSQLServer’ not found.

    We have already chosen to properly name all our Databases w/o GUIDs, and yet I get a 3760 for this specific database every hour.

    This is just one of many errors I’m chasing down, but I gotta start somewhere!

  7. 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

  8. 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.

  9. You, Sir, are a gentleman and a scholar.
    I have been trawling the internets learning SharePoint Powersell and and trying to find this answer. Congrats!

  10. Thanks a lot for posting this! It helped me clear my Secure Store error.
    While testing this out, I decided to enable search and then poke at it to get rid of the GUIDs. I’m still getting errors thrown, though. Mine is reporting an error while running the CrawlReportJobDefinition, but it’s complaining about not being able to access the Search Admin database.
    I don’t see the GUID database in either the Get-SPDatabase function or the Central Admin tool. What am I missing?

    Thanks much!
    Jennifer:)

  11. Follow to question.. Discovered the answer!
    In the Crawl database, the MSConfiguration table is holding the ID to the Search Admin database. I updated that ID to the current one (as in getting rid of the GUID, I had done a restore and the ID changed). After the update, the job started to run successfully!

    Wee!

    Thanks for these notes! Immensely helpful!!
    Jennifer:)

  12. Just fyi, I had to restart my SharePoint Timer service before I stopped getting the error messages. Other than that this worked great. Thanks!

  13. Heya, love the posts that you have made. Love to contribute for those with long database names. Just use the command:

    $bad.name

    This will show if you have that nasty DB with the GUID on its name. I know this is simple but for beginners like me, it helps.

  14. 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()

  15. 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.

  16. I’m trying to do a similar operation in 2007. Do you know of a parallel stsadm command to delete an old search database that’s not listed in the Central Admin and not on the Database Server?

  17. The get-spdatabase would truncate the long database names so I used the following which provided a cleaner view to see the names and guids.

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

    Hope that helps someone.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>