3

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
  • 1
    What SQL version? Aug 17, 2009 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, 2009 at 20:24

2 Answers 2

2

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
  • +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, 2009 at 11:27
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .