Error: New-SPEnterpriseSearchServiceApplication : A SharePoint database named xxxx already exists

The SharePoint 2010 "Search Service Application" stopped working.

I could not  crawl the data, and I could not modify the topology. The data crawling procedure was suspended there forever.

So I decided to rebuild it. Obviously this service application is corrupted, that's why I could not remove it from Central Admin site. The PowerShell seems all right.

1. Use "Get-SPEnterpriseSearchServiceApplication" to get the GUID of the search service application.
2. Use "stsadm -o deleteconfigurationobject -id [GUID]" to delete it.
3. Use "Get-SPEnterpriseSearchServiceInstance" to get the GUID of the search service instances.
4. Use "Stop-SPEnterpriseSearchServiceInstance -id [GUID]" to stop the service instances.

Now we need to rebuild it. To avoid GUID in database name, we have to use PowerShell (again). It's not easy. Lucky that Jeremy Jameson shared an excellent post about how to build it through PowerShell script

All seems OK, except the error below when running "New-SPEnterpriseSearchServiceApplication"

New-SPEnterpriseSearchServiceApplication : A SharePoint database named SP_EnterpriseSearch already exists.  You must supply another name for the new database.
At line:32 char:62
+         $searchApp = New-SPEnterpriseSearchServiceApplication <<<<  -Name $serviceAppName `
+ CategoryInfo          : InvalidData: (Microsoft.Offic...viceApplication: NewSearchServiceApplication) [New-SPEnterpriseSearchServiceApplication], SPDuplicateObjectException
+ FullyQualifiedErrorId : Microsoft.Office.Server.Search.Cmdlet.NewSearchServiceApplication

I double checked that the database "SP_EnterpriseSearch" was removed with "Search Service Application", so, what was wrong?

I had to assume this was caused by the "search service" corruption + SharePoint bug. There must be some orphaned objects left in the system.

So I removed the half-created "search service application", and then start to scan the database. With the help from Sorna Kumar Muthuraj, I searched the SharePoint Configuration database:

EXEC SP_SearchTables @Tablenames = '%', @SearchStr  = '%SP_EnterpriseSearch%'

Then I deleted all relevant objects (and their child objects) from table "SP_Config.dbo.Objects". In my case, it's totally 4 objects.

That's it.  The "Search Service Application" was created successfully after that.