Memory Usage Problem on 64 Bit SQL Server 2005 ent Cluster

My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
and there is only 16GB in the server.
I have the max memory for SQL server set at 12582912 (12GB) but the process
is running at 15,388,052 ignoring the maximum settings.
Thus the server is swapping memory to disk.
How can i make sure the SQL server is limited in its memory usgage if it is
ignoring the setting.
Regards
TobyDid you change the setting after the server was already running? Decreasing
memory won't necessarily release reserved mem without a reboot.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TobiR" <TobiR@.discussions.microsoft.com> wrote in message
news:147CE1A0-1D90-431F-960A-E44BF36B695F@.microsoft.com...
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of
> RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the
> process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it
> is
> ignoring the setting.
> Regards
> Toby
>|||Also note that max memory setting applies to the buffer pool only. The SQL
Server process consumes more memory than just its buffer pool, although the
buffer pool is often the largest, or at least should be, memory consumer.
Linchi
"TobiR" wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
>|||On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
12GB is 12288MB (which is what the max memory setting is in). Where
are you seeing max memory set to 12582912?|||Thanks for spotting my math issues! I had it set to 12TB instead of 12GB,
noticed it the other day, no wonder it was using eything it could get.
Thanks
Toby
"Jeffrey Williams" wrote:
> On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> > My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> > and there is only 16GB in the server.
> >
> > I have the max memory for SQL server set at 12582912 (12GB) but the process
> > is running at 15,388,052 ignoring the maximum settings.
> >
> > Thus the server is swapping memory to disk.
> >
> > How can i make sure the SQL server is limited in its memory usgage if it is
> > ignoring the setting.
> >
> > Regards
> >
> > Toby
> 12GB is 12288MB (which is what the max memory setting is in). Where
> are you seeing max memory set to 12582912?
>

Memory Usage on 64bit Cluster

My SQL Server Ent 2006 64Bit Active / Passive Cluster is using 16.3GB of RAM
and there is only 16GB in the server.
I have the max memory for SQL server set at 12582912 (12GB) but the process
is running at 15,388,052 ignoring the maximum settings.
Thus the server is swapping memory to disk.
How can i make sure the SQL server is limited in its memory usgage if it is
ignoring the setting.
Regards
TobySorry SQL 2005 (typeo)
"TobiR" wrote:
> My SQL Server Ent 2006 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
>

Memory Usage Active - Active

Dear all,
i am planing to implement a Windows 2003 Cluster with MS SQl 2000
Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GB
RAM per Node.
I have the need for 6 SQL instances and would like to implement a
active-active cluster. What do you think it the best way to configure
the memory for the Servers ?
I would like to run 3 instanced per Node but can i allocate 7 GB per
Node for SQL (and 1 for the Operating System) ?
What is happening then i one server fails ?
Should i plan to allocate only 3 GB per Server for SQL to make sure
that one server can handle the load for all instances if one server
fails ?
Should i use the /AWE switch only in the boot.ini to allow more than 4
GB Memory ?
or should i use the /3 GB switch as well ?
Maybe somebody can give me a hint.
Best regards,
Walter
Answers inline...
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Walter" <2oznet@.gmx.de> wrote in message
news:58bb24fd.0412172354.505a3c4a@.posting.google.c om...
> Dear all,
> i am planing to implement a Windows 2003 Cluster with MS SQl 2000
> Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GB
> RAM per Node.
> I have the need for 6 SQL instances and would like to implement a
> active-active cluster. What do you think it the best way to configure
> the memory for the Servers ?
Which instances need the most RAM? That is a lot of instance for only two
machines.

> I would like to run 3 instanced per Node but can i allocate 7 GB per
> Node for SQL (and 1 for the Operating System) ?
Again, what are the database/instance requirements.

> What is happening then i one server fails ?
>
You will then failover and have one heck of busy server!!! All 6 instances
will run, but I bet a lot slower.

> Should i plan to allocate only 3 GB per Server for SQL to make sure
> that one server can handle the load for all instances if one server
> fails ?
Can your applications handle that config? That is not a lot of memory for 3
copies of SQL to run under. Not a lot by any stretch.

> Should i use the /AWE switch only in the boot.ini to allow more than 4
> GB Memory ?
Does your hardware support /AWE? If so, yes.

> or should i use the /3 GB switch as well ?
Yes!

> Maybe somebody can give me a hint.
I would love to know your business factors that requir 6 instances of SQL.

> Best regards,
> Walter
|||There might be some dev/test cases where eveyrone wants to have the sysadmin
access to the SQL instance. It then may make sense to give each a separate
instance with the sysadmin rights to keep them isolated from each other. But
I agree you really need to think hard whether there is sufficient rationale
for having six SQL instances in a two-node cluster with 4-way servers.
Linchi
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:usaAppQ5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Answers inline...
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Walter" <2oznet@.gmx.de> wrote in message
> news:58bb24fd.0412172354.505a3c4a@.posting.google.c om...
> Which instances need the most RAM? That is a lot of instance for only two
> machines.
>
> Again, what are the database/instance requirements.
>
> You will then failover and have one heck of busy server!!! All 6 instances
> will run, but I bet a lot slower.
>
> Can your applications handle that config? That is not a lot of memory for
> 3 copies of SQL to run under. Not a lot by any stretch.
>
> Does your hardware support /AWE? If so, yes.
>
> Yes!
>
> I would love to know your business factors that requir 6 instances of SQL.
>
>

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?
You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Patricia" wrote:

> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>
|||Both nodes are affected.

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Patricia" wrote:

> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>|||Both nodes are affected.

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"Patricia" wrote:
> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>|||Both nodes are affected.

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/de...v_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.
Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:

> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/de...v_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:
> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/d...ev_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:

> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/d...ev_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> & #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory Management on cluster

I have an active/active two node cluster, each node runs Windows 2000
Advanced Server and SQL 2000 Enterprise Edition. Each node has 3.7 GB
of memory. The 3GB switch is enabled on both nodes.
When SQL Server on nodeA fails over to nodeB, will this instance only
have .7 GB of memory to use? Or can the two SQL instances share the 3
GB?<emnova@.gmail.com> wrote in message
news:1149780738.792282.241150@.j55g2000cwa.googlegroups.com...
>I have an active/active two node cluster, each node runs Windows 2000
> Advanced Server and SQL 2000 Enterprise Edition. Each node has 3.7 GB
> of memory. The 3GB switch is enabled on both nodes.
> When SQL Server on nodeA fails over to nodeB, will this instance only
> have .7 GB of memory to use? Or can the two SQL instances share the 3
> GB?
>
SQL Server will yield memory when the system is under memory pressrure.
However the process is slow, and will be painful in case of a failover. If
you really want to guarantee good performance on failover, you should add
memory or trim the max memory size of each virtual server.
David|||<emnova@.gmail.com> wrote in message
news:1149780738.792282.241150@.j55g2000cwa.googlegroups.com...
> I have an active/active two node cluster, each node runs Windows 2000
> Advanced Server and SQL 2000 Enterprise Edition. Each node has 3.7 GB
> of memory. The 3GB switch is enabled on both nodes.
> When SQL Server on nodeA fails over to nodeB, will this instance only
> have .7 GB of memory to use? Or can the two SQL instances share the 3
> GB?
They're share it (I'm assuming you don't have PAE enabled also, which you
shouldn't in this case.)
I'd probably test this though and see if I could limit say nodeB to 1.5 gig
and see what that does to performance.
Ideally, if you can, upgrade the RAM in both machines.
>

Memory Management on cluster

I have an active/active two node cluster, each node runs Windows 2000
Advanced Server and SQL 2000 Enterprise Edition. Each node has 3.7 GB
of memory. The 3GB switch is enabled on both nodes.
When SQL Server on nodeA fails over to nodeB, will this instance only
have .7 GB of memory to use? Or can the two SQL instances share the 3
GB?<emnova@.gmail.com> wrote in message
news:1149780738.792282.241150@.j55g2000cwa.googlegroups.com...
>I have an active/active two node cluster, each node runs Windows 2000
> Advanced Server and SQL 2000 Enterprise Edition. Each node has 3.7 GB
> of memory. The 3GB switch is enabled on both nodes.
> When SQL Server on nodeA fails over to nodeB, will this instance only
> have .7 GB of memory to use? Or can the two SQL instances share the 3
> GB?
>
SQL Server will yield memory when the system is under memory pressrure.
However the process is slow, and will be painful in case of a failover. If
you really want to guarantee good performance on failover, you should add
memory or trim the max memory size of each virtual server.
David|||<emnova@.gmail.com> wrote in message
news:1149780738.792282.241150@.j55g2000cwa.googlegroups.com...
> I have an active/active two node cluster, each node runs Windows 2000
> Advanced Server and SQL 2000 Enterprise Edition. Each node has 3.7 GB
> of memory. The 3GB switch is enabled on both nodes.
> When SQL Server on nodeA fails over to nodeB, will this instance only
> have .7 GB of memory to use? Or can the two SQL instances share the 3
> GB?
They're share it (I'm assuming you don't have PAE enabled also, which you
shouldn't in this case.)
I'd probably test this though and see if I could limit say nodeB to 1.5 gig
and see what that does to performance.
Ideally, if you can, upgrade the RAM in both machines.

>