db cache corruption

Dave_Jackson

I recently saw a slow down in my SSMS environment, V 2016. This was causing a short, but frustrating pause every time I opened a new query window or switched connections. Then in one particular database on one server the suggestions and autocomplete stopped working altogether. Snippets such as ssf still worked, but tabbing after the * simply inserted a tab. Annoyingly, hovering near the * showed the column list in a popup.

I un-installed and reinstalled Version 9.0.1.3179. This had no effect. I then renamed the folder "C:\Users\<username>\AppData\Local\Red Gate\SQL Prompt 9" to "SQL Prompt 9 Backup" and restarted SSMS.

I'm happy to report all is now well. I still have the "SQL Prompt 9 Backup" folder. Do you want me to zip it up and send it to you for troubleshooting?

Dave Jackson

FabiolaB

Hi @Dave_Jackson,

Thank you for reporting this. Can you please send a zip containing both SQL Prompt 9 and SQL Prompt 9 Backup folders?

Thanks,
Fabiola

Dave_Jackson

Please find attached said zip file.

FabiolaB

Hi @Dave_Jackson ,

Thank you for sending the files.

I've taken a look, and the only thing that could explain this is that in your previous settings you had the Only load suggestions for certain databases option selected, and then you had defined list of authorized databases. In your current settings, you have Load suggestions for all databases.
I suppose that the database you we're trying to get suggestions for wasn't in the authorized list, or maybe the database was renamed.

Or, the suggestions have not been refreshed (from the Prompt menu), after a database modification (eg: columns added etc).

I hope this helps,
Regards,
Fabiola

Dave_Jackson

Thanks for answering, however this was an earlier attempt to speed it up. Also the DB that SQL Prompt stopped working completely in was in the list!

HTH
Dave

FabiolaB

Hi @Dave_Jackson ,

If you encounter this problem again, please let us know, so we can try to have more info about what's happening (logs, maybe ask you to do certain operations, etc).

Thanks,
Fabiola