System.OutOfMemoryException

tpeters

Hello,

I have SQL Prompt installed on my laptop with 12GB memory. When I connect to a database with ~165,000 tables SQL Prompt will load, albeit slowly, and then work for a while before throwing the following message.

"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

Restarting SSMS will temporarily resolve the issue. Plenty of memory appears to be available via resource monitor while this is occurring. Is there a way to increase the available memory for SQL Prompt?

Thanks,
Tim

Ali D

Hi Tim

Unfortunately there is no setting to increase the amount of memory available to SQL Prompt. The problem is that SSMS is a 32 bit process and as SQL Prompt is an add-in for SSMS, SQL Prompt must share its memory space with SSMS.

You might be able to improve this behaviour by uninstalling any other add-ins you have for SSMS.

You can also manage which databases SQL Prompt connects to, more details here:
https://documentation.red-gate.com/display/SP7/Managing+connections+and+memory

By excluding connections to the large database (or excluding connections to other databases if you still want suggestions for the large database), you’ll free up some memory.

We do have some plans to work around these memory constraints in future, but this work won’t be started for a while.

Thanks
Alasdair

ddejc

Hi

I'm having same issue like tpeters when using SQL Prompt with DBs having large number of tables. I'm aware of the problem that SSMS is 32 bit software and that SQL Prompt is addin for SSMS... Is there a way to free memory which SQL Prompt is using without the need to reopen SSMS?

I'm also using Tab History. Does this add-in also using memory or does it store its data to disk? Currently my settings are 2MB max tab size and max 20 tabs to restore. Would be helpfull lowering max tabs to restore?

Ali D

Hi

You can attempt to free up some memory by clearing out SQL Prompt’s cache. To do this, uncheck Enable Suggestions in the SQL Prompt menu (or pressing Ctrl+Shift+P) then enable them again using the same option (or pressing Ctrl+Shift+P again).

SSMS’s native Intellisense also uses a fair bit of memory. You could try disabling it completely by going to Tools > Options > Text Editor > Transact-SQL and unchecking the Enable Intellisense checkbox.

Tab history does store its data to disk so that should not be affecting memory usage. However, if you have a lot of tabs open at once, closing some of them should also free up some memory.

Thanks
Ali

ddejc

Hi

Great, thanks for your help!
I've disabled SSMS native Intellisense in the next minute when I've installed SQL Prompt

Thanks
Dejan

NCTideFan

I have excluded all but about 6 databases and I still get these outofMemory exceptions on a very regular basis... particularly after using the SQL Search functionality.. I think telling people to disable parts of your product to save memory (my machine has 16 GB!), does not speak well of your product and, at least in some cases, doesn't work anyway. I've resorted to opening separate instances of SQL Management Studio whenever I need to use SQL Search; so as to avoid crashing my main instance, but this is not a workable solution.. I am starting to research replacement options for our development team; which stinks because other than these problems it's a good product.

Is this something you all are looking to fix anytime soon?

Michael Clark

Hi @NCTideFan,

Thanks for reporting, we've made a support ticket for this so they will be investigating the problem shortly.

We want to eliminate Out Of Memory exceptions, so any detail you have are immensely valuable. Like you mentioned, it seems to be worse when running more tools -- this can make it complex to diagnose but we do realise it is a key area for improvement.

Best,
Michael

erenken

I just upgraded SSMS to 17.2 and I now get this error with when I try to run queries. I uninstalled SQL Prompt and these errors stopped. In SSMS 17.1 or 17.0 I never had this issue. There appears to be a problem in the way SQL Prompt interacts with SSMS 17.2 and it is going to make me leave SQL Prompt uninstalled. Do you want any memory dumps or anything?

krzysztofkroczak

Hi @erenken,

Thank you for reporting that. We have already continued this topic in: ssms 17.2 out of memory. Please let us know if we can help more.

neo302

I was having the out of memory issue over and over. I uninstalled RedGate TSQL T and it finally worked.

silverbullettruck

I would recommend updating to SSMS 2017.3. The latest version specifically addresses the System.OutOfMemoryException. I installed it about 3 weeks ago and I have not experienced a System.OutOfMemoryException since then.