How to reclaim memory from SQL server

Question

Post reply

How to reclaim memory from SQL server

Ryan007

SSCrazy Eights

Points: 8275
More actions
November 18, 2010 at 7:34 am

#241404

hi,
In our production server SQL server has consumed almost all the memory. Please let me know how to get the memory back without inrrupting the production.
In task manager the memory shows like this:
Total: 15.8 GB
Cached: 520 KB
Free: 50 KB
Ryan
//All our dreams can come true, if we have the courage to pursue them//

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Is this a 64 bit or 32 bit? Did you set up maximum memory for SQL Server?

-Roy

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 2

It is 64 bit system and running SQL server enterprise edition in cluster environment(45 node). Max. memory is not set. But I want the temporary solution now.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

As per MS, To reduce the max server memory you may need to restart SQL Server to release the memory.

The only way to release memory is to use DBCC commands. But they will probably cause more problems than what you are facing now.

-Roy

Adiga One Orange Chip Points: 27284 More actions · Answer 4

Agree with Roy. Restarting the instance would be a safe idea

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

muralijillellamudi Right there with Babe Points: 775 More actions · Answer 5

Hi

There is no way to claim back the memory , have to restart.

Cheers

Murali

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

murali.Jillellamudi (11/18/2010)
Hi
There is no way to claim back the memory , have to restart.
Cheers
Murali

To free up memory you can run

DBCC FREEPROCCACHE will free procedure cache

DBCC FREESYSTEMCACHE will remove all unused cache

DBCC FREESESSIONCACHE will remove distributed query connection cache.

BUT, this will cause more problems.

-Roy

muralijillellamudi Right there with Babe Points: 775 More actions · Answer 7

DBCC...

These commands clears the allocated buffers but does release the memory taken by SQL SERVER

Thanks

Murali

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

SQL Server will release memory when some other operation of OS needs it.

But in this case, we are not sure if there is a memory pressure or not. Only thing we know is what the task manger shows. And task manger is not the best way to find out what is the memory used. It is better to use perf mon or use DBCC commands to see if there is a memory usage.

-Roy

Paul White SSC Guru Points: 150467 More actions · Answer 9

Sumanta Roy (11/18/2010)
It is 64 bit system and running SQL server enterprise edition in cluster environment(45 node). Max. memory is not set. But I want the temporary solution now.

Set the 'max server memory' configuration option to an appropriate value, the setting is dynamic and does not require a service restart.

From the information given, it sounds as though the account SQL Server runs under does not have the Lock Pages In Memory (LPIM) right. It is often beneficial to do so to avoid paging SQL Server out. If you do set LPIM, you absolutely must set the 'max server memory' option to an appropriate value.

For more information, see: http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi