how to release memory from sql

Question

I'm writing a program to search tables contain a certain tables in a server. It increase the memory usage of sqlserver dramatically. It seems that it's because the queries of the searching that cause it. How can I release the memory cuz it's just one time deal and i need that memory to do other things?

Answer 1

Are you talking about RAM memory usage? If yes, then that's the expected behavior. Of course, you could optimize your queries, but the idea is that SQL Server should bring data into memory to be able to do its work and if you configure it to use up to X GB then it will use it. Other queries which run after yours will ask for different data and SQL Server will move the other data into memory and move out your query data if they are not being referred - that will happen automatically. Again, that's quite normal - that's the way things work in SQL Server.

Please explain - what's your concern? One potential issue I see is this: you refer to tables which aren't being used by other 'normal' queries so when you run your queries, SQL Server 'unloads' other 'good' data to make some room for your data and after you are done, it has to read it again from disk during the first usage which will impact the performance.

Thanks,

---

Varsham Papikian, New England SQL Server User Group Executive Board, USA: www.nesql.org; http://www.linkedin.com/in/varshampapikian

Answer 2

Run the maintainess plan and shrink log.

if you are asking about physical memory restart your sql server.

you can also shrink you sql server log using DBCC SHRINKDATABASE('dbname',size)

---

Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

Answer 3

Are you talking about releasing the memory used by the SQL Server process back to the OS? If so, you don't really have to do anything. SQL Server adjusts its memory utilization dynamically by default. If there isn't a load running on SQL Server that requires the memory and other apps on the same machine requires more memory, SQL Server will release the memory it's holding. It's not instantaneous but usually reasonably quick.


joe.

---

No great genius has ever existed without some touch of madness. - Aristotle

Answer 4

Chen,

As outlined by Varsham, its the way SQL Server works. It grabs as much memory assigned to it (and available) unless it is limited by using the max. server memory setting like below for SQL Server.

sp_configure 'max server memory', 12288
RECONFIGURE
GO

As Mr.Varsham suggested, add more details about your concerns.

---

| Sankar Reddy | http://sankarreddy.spaces.live.com/ |

Answer 5

If you really need's to drop memory consumption of SQL Server without there being any external pressure you can do so by changing the max server memory configuration. To resove the memory utilization alarms.

From a client connection issue this command:

sp_configure 'max server memory', <mb setting>

reconfigure

You should see a rapid decline in the amount of memory used by the sql server process.

sp_configure 'max server memory', <mb setting> -- map it again to actual utilization

reconfigure

OR we can manage the above using GUI

Hope this helps...

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 6

Change your database option to simple and then perform your search. It will not consume much memory and if there is some extra memory reserved by Log file then use

database properties and change AutoShrink to True.

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Answer 7

Change your database option to simple and then perform your search. It will not consume much memory and if there is some extra memory reserved by Log file then use

database properties and change AutoShrink to True.

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Are you joking?

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 8

am i ?

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Answer 9

Hi,

SQL Server will free automatically all memory that can be freed and will avoid paging. If you encounter paging then the 99% memory is in use, is not available to be freed. You need to investigate how is the memory used, it is likely external components like sp_oa_xxx created object or distributed queries. Start by investigating the memory consumers, look at sys.dm_os_memory_clerks and read on How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005.

As a side note, you already have the means to automatically close databases that are not is use: alter database <dbname> set auto_close on:

AUTO_CLOSE: When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

If you host hundreds of databases that are used seldom then AUTO_CLOSE is exactly what you're looking for.

Answer 10

Change your database option to simple and then perform your search. It will not consume much memory and if there is some extra memory reserved by Log file then use

database properties and change AutoShrink to True.

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

This doesn't make any sense to me. Changing to simple recovery doesn't help reduce memory utilization and in the case of the OP trying to search, this isn't even in the same ballpark. Also, randomly changing recovery models without knowing what the system requirements are is at the very minimum reckless.

The log file doesn't consume memory. The log buffer does take up some memory but that's tiny since it does flush frequently.

Setting autoshrink to true doesn't help with memory utilization of the OP's search query either. In fact, this might increase overall memory utilization when the shrink is taking place.

@CloudChen please post more details on what exactly you're trying to accomplish including code snippets so folks here can better assist.

Answer 11

This is so wrong. You must be joking...

Answer 12

Hi,

Please Check your Server Config Values using SP_CONFIGURE and look/Think About your Min & Max Server Memory Options, It should be in SQL:OS = 80% : 20% Ratio of Servers RAM ( Its Just my opinion, not any Standard).

Regards,
Virendra Yaduvanshi
http://wikidba.wordpress.com/

---

Thanks & Regards, Virendra Yaduvanshi

Answer 13

Andrew, He is Really Joking.

Regards,

Virendra Yaduvanshi,
http://wikidba.wordpress.com/

---

Thanks & Regards, Virendra Yaduvanshi

Answer 14

You should have max memory set on the server. Typically if you have 32GB of memory, you may set max memory to 27GB.

On some operating systems you have to set "Lock Pages in Memory" as well to stabilize memory usage.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012