SQL server memory won't release

SQL server memory won't release

Post by Wile E. Coyot » Sat, 06 Nov 1999 04:00:00



Hi and thank you for looking

Here's my problem:  we have SQL server 7.0 running on a Dell server, with
1GB of ram.  Lately, some consultants began to install reports available
through our intranet by using synergy and cold fusion.  They did complex
reports that used many queries at a time.  However it seems that whenever
they run a nested query or many queries to display a report, the memory used
by the server is not released.

If I am looking at the task manager, I can see that whenever they execute a
report, the memory goes up, sometimes some of it is released but not always.

The server has 1GB of memory, and in the SQL server settings I changed the
'available memory to use' to max 800 mb, but it still doesn't release it
after.  The transaction log is set at 750 mb, but if I look at it it's not
even one third in use, and the option 'truncate log on checkpoint' is
checked.

We are now at a point where we must reboot the server several times a day,
and it is becoming quite frustrating.  Is there any setting or temporary
tables we could change ?


Thank you for your time,
Simon

 
 
 

SQL server memory won't release

Post by Hal Berenso » Sat, 06 Nov 1999 04:00:00


Do you have SP1 installed?

--
All opinions expressed here are mine and mine alone

-----------
Show support for SQL Server and support the American Red Cross, order your
books from barnesandnoble.com at
http://bn.bfast.com/bfast/click/mid1349732?siteid=6710312&bfpage=h



> Hi and thank you for looking

> Here's my problem:  we have SQL server 7.0 running on a Dell server, with
> 1GB of ram.  Lately, some consultants began to install reports available
> through our intranet by using synergy and cold fusion.  They did complex
> reports that used many queries at a time.  However it seems that whenever
> they run a nested query or many queries to display a report, the memory
used
> by the server is not released.

> If I am looking at the task manager, I can see that whenever they execute
a
> report, the memory goes up, sometimes some of it is released but not
always.

> The server has 1GB of memory, and in the SQL server settings I changed the
> 'available memory to use' to max 800 mb, but it still doesn't release it
> after.  The transaction log is set at 750 mb, but if I look at it it's not
> even one third in use, and the option 'truncate log on checkpoint' is
> checked.

> We are now at a point where we must reboot the server several times a day,
> and it is becoming quite frustrating.  Is there any setting or temporary
> tables we could change ?


> Thank you for your time,
> Simon


 
 
 

SQL server memory won't release

Post by quak » Sun, 07 Nov 1999 04:00:00


I have service pak 1 installed and I have the same trouble.  I found a
quicker (temporary) fix than rebooting my server.  If you stop and then
re-start the service you will free up the memory that is being used.

I would also like to know a permenant fix.  Any ideas?

Brad


> Do you have SP1 installed?

> --
> All opinions expressed here are mine and mine alone

> -----------
> Show support for SQL Server and support the American Red Cross, order your
> books from barnesandnoble.com at
> http://bn.bfast.com/bfast/click/mid1349732?siteid=6710312&bfpage=h



> > Hi and thank you for looking

> > Here's my problem:  we have SQL server 7.0 running on a Dell server,
with
> > 1GB of ram.  Lately, some consultants began to install reports available
> > through our intranet by using synergy and cold fusion.  They did complex
> > reports that used many queries at a time.  However it seems that
whenever
> > they run a nested query or many queries to display a report, the memory
> used
> > by the server is not released.

> > If I am looking at the task manager, I can see that whenever they
execute
> a
> > report, the memory goes up, sometimes some of it is released but not
> always.

> > The server has 1GB of memory, and in the SQL server settings I changed
the
> > 'available memory to use' to max 800 mb, but it still doesn't release it
> > after.  The transaction log is set at 750 mb, but if I look at it it's
not
> > even one third in use, and the option 'truncate log on checkpoint' is
> > checked.

> > We are now at a point where we must reboot the server several times a
day,
> > and it is becoming quite frustrating.  Is there any setting or temporary
> > tables we could change ?


> > Thank you for your time,
> > Simon

 
 
 

SQL server memory won't release

Post by James Wilso » Sun, 07 Nov 1999 04:00:00


SQL Server will not release the memory immediately because it thinks that
you need the data loaded into memory buffers (and you do as you have just
run a report on this data).  SQL Server does not know (and cannot know) that
you are now finished with data and that it should be written back to disk
and memory released.  This data will remain in the buffers, as all data
remains in the buffers, until SQL Server needs to swap it out for new data
or another program is started on the NT Server that needs memory.  SQL
Server will then release as much as it can from its allocation if the data
those memory buffers have not be referenced for sometime.

--

 
 
 

SQL server memory won't release

Post by Jerry Spive » Sun, 07 Nov 1999 04:00:00


Wile - SQL Server will only release the memory if there is a request for
memory from additional applications that exceed 5mb +/- 200kb to prevent NT
paging.  Adjust the max memory setting if necessary.

--
HTH
--
Jerry Spivey
MCT, MCSE, MCSD
Senior SQL Instructor - Consultant
ARIS Corporation  Bellevue, WA
(Please reply to the newsgroup only, not by email.)



> Hi and thank you for looking

> Here's my problem:  we have SQL server 7.0 running on a Dell server, with
> 1GB of ram.  Lately, some consultants began to install reports available
> through our intranet by using synergy and cold fusion.  They did complex
> reports that used many queries at a time.  However it seems that whenever
> they run a nested query or many queries to display a report, the memory
used
> by the server is not released.

> If I am looking at the task manager, I can see that whenever they execute
a
> report, the memory goes up, sometimes some of it is released but not
always.

> The server has 1GB of memory, and in the SQL server settings I changed the
> 'available memory to use' to max 800 mb, but it still doesn't release it
> after.  The transaction log is set at 750 mb, but if I look at it it's not
> even one third in use, and the option 'truncate log on checkpoint' is
> checked.

> We are now at a point where we must reboot the server several times a day,
> and it is becoming quite frustrating.  Is there any setting or temporary
> tables we could change ?


> Thank you for your time,
> Simon

 
 
 

1. SQL Server don't release memory

Hi all,

I have SQL Server 7.0 SP2 installed on a NT 4.0 Server SP6 machine.

The problem is that SQL-server __ EAT MEMORY __.
The machine I run SQL-server on has 256 MB of Ram and the MEMORY that
sqlserver use keeps growing and growing until the only solution is to close
sqlserver and start it again. It not release memory also after some days of
inactivity!!

Anyone know how "force" SQL Server to release memory whitout close it?

Any ideas would be greatly appreciated

Thanks in advance,
Stefano

2. New tips, Easter egg and free support for Access

3. SQL Server doesn't release memory

4. SQL Enterprise Manager error

5. R-tree

6. SQL Server 6.0 won't start - changed memory config

7. Fatal error in shared memory creation

8. NT Developer server won't release .fmx

9. SQL Server won't start on server with multiple IP's

10. Confingured too much memory, server won't start

11. BCP takes lot's of memory and doesn't release it

12. Upgrade to win xp and ms sql server won't start