sql server 7 memory leak
tjs
we're using sql 7 on an nt 4 server with sp-6, and an access 97 front-end.
at the beginning of last week the system was working fine.
by the end of the week, we were running out of all physical memory on the
server. when we check the task manager to see which processes are using the
most memory, sqlsvr.exe is the culprit, using almost all the memory on the
box.
the only thing we seem to have changed is we are beginning to develop some
ASPs that access data on the sql server-- but the memory seems to vanish
even if no one runs any ASPs.
anyone have any ideas?
thanks
trevor steinberg
Neil Pike
> by the end of the week, we were running out of all physical memory on the
> server. when we check the task manager to see which processes are using the
> most memory, sqlsvr.exe is the culprit, using almost all the memory on the
> box.
>
Q. Does SQL Server have memory leaks? How can I tell? Why is SQL Server using so much memory? What
do I do about virtual memory errors?
(v1.6 2000.01.29)
A. Generally speaking SQL Server doesn't have much of a problem with memory leaks and it is almost always
other programs/drivers that cause the problem. For specifics on known SQL Server bugs causing memory leaks
see the end of the faq entry. It's always a good idea to apply the latest service pack if you haven't
already - MS do fix these bugs as soon as they find them.
Remember that SQL Server will grab memory for it's data cache up to the amount you have specified via
sp_configure. This is in 2K pages (for SQL 6.5) and in 1MB chunks for SQL 7.0 and above. This amount does
not include any amount for tempdb in ram (6.5 and below only - SQL 7.0 and above don't support tempdb in
ram) or for the SQL kernel and some other internal memory structures. It would not be unusual for the SQL
kernel and other memory structures to use an extra 10-20MB of ram. In addition to this it also requires
o/s buffers and memory for things like BULK INSERT, OLE/COM programs etc. These don't come out of the
memory that SQL allocates for data cache.
Therefore if you configured SQL Server to use a maximum of 50MB ram, then don't worry until the memory
allocated to sqlservr.exe goes over, say, 70MB.
With SQL 7.0 the default is to dynamically allocate memory - though you can set an upper limit if you wish.
SQL Server 7.0 will keep grabbing memory, up to the set limit (default no limit), until NT tells it that
other processes need the memory. On a dedicated machine this won't happen, so it is not unusual for SQL
7.0 to seem to keep grabbing more and more memory.
After reading the above explanations, if you still think you have a memory leak then run performance
monitor and select the processes object. Choose all running processes (make sure everything you normally
run is going at the time) and for these choose the paged pool, non-paged pool and virtual bytes objects.
Put these on a chart or log with a long interval period. Monitor these objects over time to see what
always increases. If it is SQL Server (sqlservr.exe) continues increasing above the maximum memory (+20MB)
it should have allocated then it may be responsible for a memory leak. You could also check for handles
and threads always increasing as these could also potentially be leaking - and running out of these is just
as bad as running out of memory.
You can also get these parameters from task manager - go into the process view, choose view columns and add
the relevant columns.
If no processes in task manager/perfmon show a memory leak, but the overall memory is still going up, then
the leak must be down at the kernel level. To trace this use the poolmon.exe program. Instructions to use
this are in Microsoft Technet and the Microsoft knowledge base site - just search for poolmon.
-----------------------------------
Known memory leak issues :-
1. If you are running the Novell network client v4.5 or above on the server then you may experience a
memory leak. Go back to version 4.11 to resolve. (I don't know whether this is an Microsoft bug or a
Novell one).
2. SQL 7.0 RTM had a couple of leaks that were resolved in SP1.
3. If you do 1000's of BULK INSERT's then you will notice a fairly significant memory leak with SQL 7.0
RTM and SP1. This will be fixed in 7.0 SP2. (Or contact Microsoft PSS for a post-SP1 hot-fix)
4. SQL 6.5. If you utilise performance counters and connect using the local computer name then a leak may
occur in WinLogon.exe. (NT Performance monitor doesn't cause the problem). See Q249343 for more
information. The problem will be fixed in 6.5 SP6.
5. If you use xp_cmdshell with the @attached_results option on SQL 6.5 then this leaks memory. Contact MS
PSS or apply SP6 (when available)
Neil Pike MVP/MCSE. Protech Computing Ltd
(Please reply only to newsgroups)
SQL FAQ (412 entries) see
sqlfaq.zip in lib 7 (SQL Public) @ http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
or www.ntfaq.com/sql.html (+ ntfaq download)
or http://www.sql-server.co.uk
Tomasz Kopacz
wrote:
>hopefully someone there can help us with this
>
>we're using sql 7 on an nt 4 server with sp-6, and an access 97 front-end.
>at the beginning of last week the system was working fine.
>
>by the end of the week, we were running out of all physical memory on the
>server. when we check the task manager to see which processes are using the
>most memory, sqlsvr.exe is the culprit, using almost all the memory on the
>box.
>
>the only thing we seem to have changed is we are beginning to develop some
>ASPs that access data on the sql server-- but the memory seems to vanish
>even if no one runs any ASPs.
>
>anyone have any ideas?
>
>thanks
>
>trevor steinberg
I think, that SQL get's all memory for internal cache. Try to exactly
set among of memory which SQL 7 could use...
It should help.
Or maybe you have not "standard" network library
--
Pozdrawiam,
Tomasz Kopacz
tko...@idg.com.pl