Hi everyone,
SQL server 2000 is eating up the memory as we are executing queries. Looks like SQL allocates memory to each result set. Does anybody know how to refresh memory after each query execustion?
Thanks,
-Ned

__________________
-Ned

If you don't set a ceiling on memory usage SQL Server will use as much memory as is available. When it has aquired memory it will not release it, unless you drop the ceiling. This is by design and is not a problem. Just set a ceiling that leaves enough space for whatever else is running on the machine.

regards
David Cameron

David,
Can you explain me how can I set & drop ceiling?

Thanx,
-Ned

-Ned

If I may drop in here...

Enterprise Manager, right click the server, then Properties, Memory tab.

You can adjust the minimum and maximum memory used by SQL Server on this tab.

SQL Server generally runs best with the 'Dynamically configure SQL Server memory' option selected. You should use the Fixed memory option only under special circumstances (like you know what you are doing!) as you can starve other server processes if you are not careful.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff,
This does not work in my case, I have already tried that.
Take the following scenario:
If you open the Task Manager, see sqlservr.exe Mem usage under Processes. Now open the SQL query analyzer and issue some SELECT statement and watch how the Mem Usage is increased, and it will keep increasing as you issue more different select statements.
Even though we fix memory.
Do you have any other solution to release the memory that SQL acquire for issued queries?

-Ned

Overall memory management in SQL Server is complex. I suggest you read the topic "Dynamically Managing Memory on Windows NT and Windows 2000" in BOL.

Generally and simply speaking, SQL Server will consume just about as much memory as is physically available, stopping just short of having the system start paging. This memory is allocated on demand. As other processes require memory, it will begin to release memory back to the system as the system needs it.

The settings in EM only influence the size of the database buffer pool but SQL Server needs memory for lots of other things, like the query execution cache and buffering of the transaction log to name a couple. This usage cannot be individually contolled, I believe.

Why do you think you need to tighten the screws so much?


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff,
The reason I wanted to tight the screws, because some of my applications fails to load.
But I understand about SQL that this is the nature of the beast...
Thanks anyway,
-Ned

i'm having the same problem. any new ideas?
i'm using simple connection assurance method by seleting 1 from database (select 1); though i can inform user about a connection loss. after a week of SW trial server exceeded memory... ...and programs started to halt. i tested select 1 with a rate of 1ms and server crashed after a few hours :(

I am also having the same problem. The SQLserver process is steadily eating more and more memory even when there is no activity going on with the server. (overnight after everyone goes home)

Just some background...
Originally the memory settings were set on dynamic and the amount of memory for the SQLServer was 0 to max. After setting the Dynamic Memory settings to limit the amount of memory to less than max, I restarted the SQLServer so they would take effect.

Some of the other people were saying that if other processes need memory then SQLServer will relinquish memory, but this doesnt seem to be the case.

Anyone know why SQLServer would just continue to allocate memory for itself?????

Throughout this chain I see no answer to the underlying problem. That is, sql server does not release memory properly. So, can anyone respond with a way to have sql server release the memory when no longer needed, other than restarting the DB?