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 Comments so far

  1.   Khan on December 6th, 2010          Reply

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

  2.   Knox Cameron on December 7th, 2010          Reply

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

  3.   Bill Morris on December 8th, 2010          Reply

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

  4.   Mike Pasco on December 8th, 2010          Reply

    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}

  5.   Neel on January 20th, 2011          Reply

    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

  6.   Neel on January 20th, 2011          Reply

    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

  7.   Faye Jasman on January 20th, 2011          Reply

    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.

  8.   Earnie Eng on February 25th, 2011          Reply

    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!

  9.   zenegra online on March 15th, 2011          Reply

    Hi,
    Nice stuff posted here.Really very interesting. From so many days I was searching for this type of information . Thanks for sharing this blog…

  10.   Nava on March 28th, 2011          Reply

    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

  11.   Nava on March 28th, 2011          Reply

    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.

  12.   Patrick Roberts on May 26th, 2011          Reply

    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!

  13.   Kaumil Dalal on June 2nd, 2011          Reply

    In addition to the instructions above, I had to restart SharePoint 2010 Timer service.

  14.   Franz Goeschl on June 17th, 2011          Reply

    Thank you for this great Blog. I did it exactly along your description and it worked.

  15.   Jennifer on July 18th, 2011          Reply

    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:)

  16.   Jennifer on July 18th, 2011          Reply

    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:)

  17.   David B on July 21st, 2011          Reply

    Brilliant, thank you so much for this, reinstalled my search and had a redundant one

  18.   Imperfect IT on July 25th, 2011          Reply

    Thanks! Been looking for a less destructive way to fix that for a while.

  19.   Tom Resing on September 20th, 2011          Reply

    Shane,
    Great catch. I used this one today.
    Tom

  20.   Sean on September 21st, 2011          Reply

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

  21.   mejohnm on November 11th, 2011          Reply

    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.

  22.   PeteGriff on February 6th, 2012          Reply

    Thanks very much, easy to follow even though the pictures aren’t showing!

    I found that this link helped me ensure I had the right databases selected as the names always get truncated. http://njbblog.blogspot.com/2011/03/sharepoint-40-powershell-get-spdatabase.html

  23.   Moojjoo on March 7th, 2012          Reply

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

  24.   lonuel on April 25th, 2012          Reply

    $bad.name shows the complete database name.

  25.   PhY on May 11th, 2012          Reply

    nice job !

  26.   Eric on May 23rd, 2012          Reply

    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.

  27.   John on February 6th, 2013          Reply

    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?

  28.   Trevor on February 20th, 2013          Reply

    Pictures don’t show up!

  29.   Mitchell on May 9th, 2013          Reply

    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