Find Invalid Object not working on SQL Express 2017

TheoL

Running latest release (10.0.7.13230) in SSMS 17.9.1.
When using Find Invalid Objects on a database on local SQL Express 2017, it hangs indefinitely showing the progress bar.  Text below progress bar shows "Counting objects …" but get stuck at about 10%
Using "Find Invalid Objects" works fine on a local SQL Server 2016 instance.

Does anyone else have problems using Find invalid objects on SQL Express 2017?

TheoL

@DanC I have found the cause:
For performance, I use "Only load suggestions for certain databases/schemas" option.  My freshly installed SQL Express 2017 was not in the list.  The fact it was SQL Express 2017 was not related to the cause.

It turns out the if the database is not included in the server/database list for connections, then the Find Invalid Objects does not work.

It would be a sensible UI enhancement to notify the user that this is the case instead of allowing the attempt, then just showing a progress bar that appears to get stuck at about 10%.

DanC

Hi @TheoL could you possibly provide me with an example script you're using to find invalid objects on for me to test?

TheoL

Hi, @DanC
I was attempting to use the "Find Invalid Objects" feature of SQL Prompt, by right clicking on a database in the SSMS object explorer, then selecting the "Find invalid objects" menu item.

It is the resulting "SQL Prompt - Invalid Objects" window that hangs - there is no script involved.

DanC

Hi @TheoL unfortunately I'm unable to replicate the issue, so I'm using SQL Express 2017 and finding invalid objects on AdventureWorks2017 DB. Are you having issues with finding invalid objects on multiple databases in your problem instance?

What you can also do is enable verbose logging and perform the action and send us the logs to review and see if that gives us any answers perhaps!

Also if possible could you provide us with your Prompt settings as perhaps there's something occurring there too. 

DanC

TheoL said:

@DanC I have found the cause:
For performance, I use "Only load suggestions for certain databases/schemas" option.  My freshly installed SQL Express 2017 was not in the list.  The fact it was SQL Express 2017 was not related to the cause.

It turns out the if the database is not included in the server/database list for connections, then the Find Invalid Objects does not work.

It would be a sensible UI enhancement to notify the user that this is the case instead of allowing the attempt, then just showing a progress bar that appears to get stuck at about 10%.

Hey Theo, I've passed all the details onto the development team and they've recognized this as a bug and so they will be reviewing it in due time. 

Hope this helps!