How do I free up some RAM on our SQL Server (showing 77MB free). When I try to run a query I get an error saying cannot start thread out of RAM. It is using all but 77MB of like 5000+ MB.
2 Answers
Have you set "max server memory" option. SQL could be chewing up all available memory causing the scenario you explain to occur.
You can check and set it as below. It will not require a system reboot or sql service restart.
--Check Max memory setting
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory (MB)'
GO
If run_value is 2147483647 or more than the actual RAM on the server, set this as per actual RAM on server leaving some for the OS.
sp_configure 'max server memory (MB)', 4096 --4GB
GO
RECONFIGURE
GO
Also, you might wish to look at setting up AWE if it is a 32-bit SQL Server Enterprise
-
+1. I get this sometimes when executing a very large number of INSERT statements (automatically generated) in SSMS on an instance for our testing guys that's only given 256MB RAM in that setting. Increasing that setting allows me to execute the large set of inserts. Sep 3, 2009 at 11:27
If you have a high number of concurrent users from web-server pools, you might be able to lower the pool thread counts and free up enough resources for yourself without disrupting the system.
Alternately, you can always stop services that aren't strictly necessary for a short period while trying to free a bit.
I'm assuming here that you just want to free enough to get connected with a query window and then find out what else is going on that is eating resources. If this problem is happening regularly then I'd recommend you open a query window early in the day and run sp_who2 every few minutes to keep the connection active until the server fills up and can't create new threads.
I'd also recommend that you fire up perfmon and start monitoring SQL Server memory, connections, and DB level statistics and start looking for misbehaving apps that are hurting you. If this is happening regularly then you either have a bad bit of code out there, endemic bad programming, or a real need to upgrade.