Memory Problems in SQL2000 SP3A

Hi,
We get memory problems every so often on our Production SQL2000 SP3A server.
They are described in KB http://support.microsoft.com/kb/903002/en-us We
have made a number of our systems reduce the packet size as the article
suggests. Normally the WARNING: Failed to reserve contiguous memory message
leads to further problems and only a reboot of the server fixes the problem.
Is there something that could be done to fix the situation without shutting
down SQL and rebooting the server? Can we clear some of the buffer cache
with a DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
Thanks
ChrisChris,
That KB only talks about one of the many reasons why you can get that error.
This is what was called MemToLeave area of memory and is used for many
things that require contiguous memory space. This memory can get fragmented
from poor behaving things such as XP's and such. One example was earlier
version of SQL LiteSpeed would slowly fragment the MemToLeave area by
leaving blocks allocated and eventually there would not be enough contiguous
space for normal operation. You need to find what the culprit is of coarse
but these kbs might get you started. Look for things like heavy linked
server access, sp_oacreate, xps etc. You can also increase the size of
MemToLeave with the -g startup param which will give you more time between
restarts but wont solve the issue.
http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
http://support.microsoft.com/kb/316749/
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We get memory problems every so often on our Production SQL2000 SP3A
> server. They are described in KB
> http://support.microsoft.com/kb/903002/en-us We have made a number of our
> systems reduce the packet size as the article suggests. Normally the
> WARNING: Failed to reserve contiguous memory message leads to further
> problems and only a reboot of the server fixes the problem. Is there
> something that could be done to fix the situation without shutting down
> SQL and rebooting the server? Can we clear some of the buffer cache with a
> DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
> Thanks
> Chris
>|||Andrew,
Thanks for the info. From what I had read the MemToLeave -g switch appears
as the only solution I had seen. We will be moving from SQL2000 to SQL2005
once SP2 appears so we may not have this situation too much longer. What I
would like confirmed is that a reboot is the only solution once the messages
start to appear or is there anything else that can be done, even tell
everyone to signoff for a few minutes. Would this allow the memory to be
de-allocated so the fragmentation problem would disappear?
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Chris,
> That KB only talks about one of the many reasons why you can get that
> error. This is what was called MemToLeave area of memory and is used for
> many things that require contiguous memory space. This memory can get
> fragmented from poor behaving things such as XP's and such. One example
> was earlier version of SQL LiteSpeed would slowly fragment the MemToLeave
> area by leaving blocks allocated and eventually there would not be enough
> contiguous space for normal operation. You need to find what the culprit
> is of coarse but these kbs might get you started. Look for things like
> heavy linked server access, sp_oacreate, xps etc. You can also increase
> the size of MemToLeave with the -g startup param which will give you more
> time between restarts but wont solve the issue.
>
> http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
> http://support.microsoft.com/kb/316749/
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
>|||I guess it depends on what is causing the fragmentation but most likely no.
You need to find out what is causing it and fix that.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Thanks for the info. From what I had read the MemToLeave -g switch appears
> as the only solution I had seen. We will be moving from SQL2000 to SQL2005
> once SP2 appears so we may not have this situation too much longer. What I
> would like confirmed is that a reboot is the only solution once the
> messages start to appear or is there anything else that can be done, even
> tell everyone to signoff for a few minutes. Would this allow the memory to
> be de-allocated so the fragmentation problem would disappear?
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
>|||Thanks Andrew.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:emNMuO$OHHA.404@.TK2MSFTNGP02.phx.gbl...
>I guess it depends on what is causing the fragmentation but most likely no.
>You need to find out what is causing it and fix that.
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
>