No search results

IanC

I'm currently using SQL Search 2.4.0.1203 in SSMS 2016 & it's failing to return any results for our main development database, but appears to be working correctly for other databases on the same server (including one which is essentially a clone of our development db).
Any ideas what could cause it to not work against a particular db?

Thanks,
Ian

rkretsch17

I'm having the same issue. It seems the latest version they released has a bug (this never happened to me until about a week ago and I use this product every day). I've found if you add/remove other server connections in SSMS it now forces a reindex. I can usually get it to work again if I remove all but the one server connection.

My guess is if you connect to two servers that have a database with the same name it causes it to freak out and error.

houstonc

Just updated SQL Toolbelt Essentials today. Have version SQL Search 2.4.1203.

Getting similar results to rkretsch17. Have 2 database connections open in SSMS 2014 and it is not searching on the second connection. Works on the first connection. Disconnected the first connection. Closed and reopened SQL Search then it works.

With 2 servers that have very similar Databases (our dev and prod) SQL Search 2.4 is failing on the second connection.

On 2 servers with no overlaping databases, you can switch to either server and it works as expected.

IanC

Mine's failing to function with just a single server connection.

Bellisio

I am having the issue, thanks for the advice, when I remove all connections but the one, it is working. This is not convenient, hope they fix soon.

sqlgarden

This is happening to me today too. My version is 2.3.8.1089 and I haven't updated it recently.

I miss being able to use this.

dhardy

Setting to a single connection did not solve this issue for me. Reindex did not work. I did find that if I make a backup and then delete the contents of the folder %AppData%LocalRed GateSQLSearch 2Indexes, it would then find 139 results for my search as opposed to zero.

jsreynolds1

Deleting everything out of %AppData%LocalRed GateSQLSearch 2 didn't seem to make a difference.

Adding another server to object explorer suddenly made it start returning results.

v2.4.0.1203.

Esc

I am having no search results on a specific database on a specific server. I've had this problem with 2.2, and now seeing it with 2.4.0.1203 after a fresh update.

It looks like the problem started after I linked this database to svn source control. Not 100% sure but that's the only thing special about this database. After unlinking the database from source control, the problem persisted.

polygonuk

I get the same issue, no search results when I'm connected to multiple servers with two SSMS instances open under different users. If I'm just connected to one server in one SSMS instance then I get correct search results.

SSMS: 2016 (13.0.16106.4)
SQL Search: 2.4.0.1203

camaro322hp

Same here. Can confirm that multiple server connections seem to trigger this. My workaround is closing the SQL Search window and re-opening it, and then it will have search results.

SQL Search 2.4.0.1203
SSMS 13.0.16106.4

rauscs

Having the same problem 2.4.0.1203. Been using it for a year or two with no problems. I've delete the indexes and no go. If I click 'reIndex', it can take anywhere from 1 second to a minute, but seems to fail before it finishes. Is there a log somewhere I can look for errors? I only have a single server connection.
Steve

JenniferL

Not to throw any water on the multi-server theory, but I am experiencing the same issue and, like IanC, I'm working with a single server and SQL Search Version 2.4.0.1203. The problem may be affecting clients with multiple server connections, but it most certainly is affecting those of us with single server connections, too. Please don't overlook is.

Steve_H

This is very frustrating and has me looking for new SQL Server tools. The long index times and now this "not finding results" are deal breakers.

Jessica R

Hi all,

So sorry to hear about these issues and for the delay!

If index files get corrupted, it can often help to close SSMS and then delete the %LocalAppData%\Red Gate\SQLSearch 2\Indexes folder, though it sounds like this hasn't helped in most cases.

I'm actually not able to immediately reproduce this so I'm going to raise this with our developers. I will post here once I have more detail.

Jessica R

Hi everyone,

We've gotten a report that rebooting the machine has resolved this issue in one case, so if deleting the index files does not help, this may be worth a try.

Should it still fail though, can you all please check your most recent log file in %LocalAppData%\Red Gate\Logs\SQLSearch 2 for any errors regarding indexing your database(s) and post them here?

Thank you!

michaelmm

mine worked after dumping the contents of the index folder

Jessica R

Thanks for confirming that @michaelmm22!

LizCooper

My log file said I didn't have access to sysjobs so going to ask for this to be given:
21 Apr 2017 10:07:08.451 [27] ERROR RedGate.SQLSearch.Addin.LoggingErrorHandler - Exception while re-indexing Jobs on VXVSSMSCSQL06C:
Exception: The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.
21 Apr 2017 10:07:09.161 [81] ERROR RedGate.SQLSearch.Addin.LoggingErrorHandler - Exception while re-indexing Jobs on VXVSSMSCSQL06C:
Exception: The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

EricWhatley

I am also looking for a solution to this. Attached are a couple of log files, each from different systems experiencing the same issue. Any help would be appreciated. Thanks!

Jessica R

Hi @EricWhatley ,

It looks like the error is "database is locked" in your case.

The cause of this error is still under investigation (SDI-716), but the following usually works around this:

1) Open SSMS but do not connect to a Database Engine.
2) Open SQL Search2
3) Connect to your database engine
4) Use SQL Search

Can you please give this a try?

Flimmer

Also having this issue with SQL Search 2 v 2.4.4.1453 on SSMS 2014. Deleting index folder contents didn't help. Tried Jessica R June 14 4-step method, works for the session but closing and opening SSMS brings the issue back.

We work in a managed services environment so we can't get additional server rights to database msdb. Jobs are not allowed in our environment.

Log file when SQL Search works:
"Exception: The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'." followed by "ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxx' is not indexed".

Log files when SQL Search doesn't work filled with this:
Exception: database is locked
database is locked
16 Jun 2017 11:14:10.400 [52] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'MyDBName on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.402 [52] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.401 [8] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'MyDBName on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.407 [8] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.665 [63] ERROR RedGate.SQLSearch.Addin.LoggingErrorHandler - Exception while re-indexing MyDBName on dbxxxxxx:
Exception: database is locked
database is locked
16 Jun 2017 11:14:10.666 [49] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'MyDBName on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.667 [49] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.667 [6] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'MyDBName on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.670 [6] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.932 [65] ERROR RedGate.SQLSearch.Addin.LoggingErrorHandler - Exception while re-indexing MyDBName on dbxxxxxx:
Exception: database is locked
database is locked
16 Jun 2017 11:14:10.935 [47] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'MyDBName on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.936 [47] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.935 [54] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'MyDBName on dbxxxxxx' is not indexed
16 Jun 2017 11:14:10.964 [7] ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxxx' is not indexed
16 Jun 2017 11:14:35.258 [1] INFO RedGate.SQLSearch.UsageReporting.ApplicationUsageHelper - The reporting of product and feature usage information did not complete in the allotted time during shutdown (this will not affect normal operation of the software)

Regards and hope this helps,

Greg

Jessica R

Hi @Flimmer,

Sorry to hear that you're running into this as well!

If Jobs access will not be allowed, you can safely ignore the error, "Exception: The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'." followed by "ERROR RedGate.SQLSearch.Addin.Tab.Controller - Search failed: 'Jobs on dbxxxxx' is not indexed".

Regarding the "database is locked" issue (SDI-716), I am still looking into what causes this and for other possible workarounds, as I realize it's not ideal to have to do the current workaround on every SSMS restart!

bauerju

Hi
I have the same problem here: Win10PRo, SSMS 17.1 (14.0.17119.) SQL Search 2.4.0.1203, SQL Prompt 8.0.1.1383

Sometimes Search Fails with one database. There are no results.
I can reach the server only over vpn on a 16 Mbit line. Latencetime for a ping is over 50 MS, sometimes mor than 100.
Even Reindex doesn't help.
I got following error in Logfiles:

30 Jun 2017 07:43:29.673 [53] ERROR RedGate.SQLSearch.Addin.LoggingErrorHandler - Exception while re-indexing xxxx on yyyy:
Exception: constraint failed
UNIQUE constraint failed: sysobjects.id

Only closing of SSMS and reopening helps sometimes.

This sucks ...
Jürgen

rro

I have the same issue. Log files shows the database that I was searching an object within, was not indexed. Closing down SQL and opening helped.

drodecker

Worked for me. Closed SSMS and deleting the contents of %LocalAppData%\Red Gate\SQLSearch 2\Indexes folder worked.

ITSupport

I can see that the thread started on March 22, 2017. Has there been any progress on fixing this? Or at least is there any advise on what to do other than restart the PC and delete cache files?
We are a team of devs and all of us experience this problem on a daily basis.

Error is "database is locked" followed by a "Search failed: 'DBNAME on SERVER' is not indexed"

EricWhatley

Not sure where I read this, but it's worked for me as a workaround.

Basically, start SSMS -> Cancel out of Connect Dialog -> Open Redgate Search -> With Redgate Search Open, THEN Connect

JenniferL

It happens frequently enough that I wrote a very short (one line) batch file to delete all the files in "c:\users\<username>\appdata\local\red gate\sqlsearch 2\indexes\". When the problem occurs, I run the batch file then click Index -> Reindex on the SQL Search screen. Works every time.

UmbertoA

Just solved the problem for Windows 10 & SQL Search 3:

1. Close SQL Server Management Studio
2. Go to the folder C:\Users\<user>\AppData\Local\Red Gate
3. Delete the SQLSearch 3 folder
4. Delete the Logs\SQLSearch 3 folder

kdbajan

Unfortunately for me. The steps like UmbertoA didn't work for me. I'm about to retry the one from Jessica R as I might have done out of sequence.