Memory problem

I have an accounting program that is loaded on a server
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
hi Daniel,
"Daniel Barnaby" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:57e701c42d34$9e0bcfa0$a301280a@.phx.gbl...
> I have an accounting program that is loaded on a server
> and have 7 users. The program seem to have a memory leak.
> The has server 1 gig of memory after the program runs for
> a few hours the memory usage climes from 150meg used to
> 870meg, used all of witch is used by the sql instance. I
> then have to have eveyone get out of the program stop the
> sql server then restart it. Is there any thing out there
> that can help me(beside buying a new accounting program
> that is writen well),some type of auto resource recycling,
> or auto restarting the sql instance with out causing the
> users any to be down.
you must first understand memory usage by SQL Server...
in few words, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding)
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, in process COM objects space and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
but you are not limiting the resource, so SQL Server can reclaim that memory
(up to 1 gb, in our example) for it's uses, and it will perhaps release it
only under pressure by the OS claiming for additional memory, if it's the
case, else it will maintain that memory in order to cache pages and
execution plans...
it's quite a normal behaviour...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply