Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
1  
What SQL version? –  Remus Rusanu Aug 17 '09 at 19:24
1  
Is the server x86 or x64? What about the SQL Server? How many connections to the SQL Server do you have? –  mrdenny Aug 17 '09 at 20:24

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
+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. –  tomfanning Sep 3 '09 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.