Sql Memory Leaks: client

Showing posts with label client. Show all posts

Wednesday, March 28, 2012

Merge Control Synchronization Problem

Hi Guys - I have been using the Merge Control ActiveX successfully for
some time. However, one client has found a problem synchronizing. Here is
the relavent piece of code:
agent.Publisher = "MyServer\InstanceName";
agent.Distributor = agent.Publisher;
agent.DistributorNetwork = TCPIP_SOCKETS;
agent.PublisherNetwork = TCPIP_SOCKETS;
agent.PublisherAddress = "192.168.1.100\InstanceName,2763";
agent.DistributorAddress =
"192.168.1.100\InstanceName,2763";
This is a Merge replication with pull anonymous subscriptions. The
Publisher and Distributor are on the same server.
I am using the PublisherAddress because I need to specify the port.
The error the client gets is the following:
[vbcol=seagreen]
If we try anything else for the publisher name such as
"192.168.1.100\InstanceName" or even the fully qualified name of the server,
it says:
[vbcol=seagreen]
valid Publisher'
Now, we checked and double-checked the settings. We can log in fine to
the publisher and subscribers. As a matter of fact, we were able to
subscribe just fine.
I tested the same scenario with my computers and it works fine for me.
What could cause it to say that The process could not connect to
Distributer?
Is the Publisher name always "ServerName\InstanceName"?
Any help will be apreciated,
Thanks,
Maer
I think you are having name resolution problems from this client. Can this
client ping the publisher/distributor by IP?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maer" <maer@.auditleverage.com> wrote in message
news:%23EO95jlPGHA.2080@.TK2MSFTNGP09.phx.gbl...
> Hi Guys - I have been using the Merge Control ActiveX successfully for
> some time. However, one client has found a problem synchronizing. Here is
> the relavent piece of code:
> agent.Publisher = "MyServer\InstanceName";
> agent.Distributor = agent.Publisher;
> agent.DistributorNetwork = TCPIP_SOCKETS;
> agent.PublisherNetwork = TCPIP_SOCKETS;
> agent.PublisherAddress = "192.168.1.100\InstanceName,2763";
> agent.DistributorAddress =
> "192.168.1.100\InstanceName,2763";
> This is a Merge replication with pull anonymous subscriptions. The
> Publisher and Distributor are on the same server.
> I am using the PublisherAddress because I need to specify the port.
> The error the client gets is the following:
>
> If we try anything else for the publisher name such as
> "192.168.1.100\InstanceName" or even the fully qualified name of the
> server, it says:
> valid Publisher'
> Now, we checked and double-checked the settings. We can log in fine to
> the publisher and subscribers. As a matter of fact, we were able to
> subscribe just fine.
> I tested the same scenario with my computers and it works fine for me.
> What could cause it to say that The process could not connect to
> Distributer?
> Is the Publisher name always "ServerName\InstanceName"?
> Any help will be apreciated,
> Thanks,
> Maer
>
>
>
|||Yes, he has pinged the IP and it responds ok. Also, we can connect fine
to SQL Server (through our application) on the publisher/distributor using
"MyServer\InstanceName" or "IP\InstanceName".
BTW, the publisher/distributor is running on a Windows 2003 Server.
Maer
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23gnkoQmPGHA.2912@.tk2msftngp13.phx.gbl...
>I think you are having name resolution problems from this client. Can this
>client ping the publisher/distributor by IP?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Maer" <maer@.auditleverage.com> wrote in message
> news:%23EO95jlPGHA.2080@.TK2MSFTNGP09.phx.gbl...
>

merge client failing

We have sql2005 merge replication happening - it is using replisapi.dll over the net.

One of the clients has been working fine, until yesterday afternoon - we are running sql2005 sp1 at subscriber and distributer

The message is as follows....

Please help !

Error messages:

The process could not read the request message due to OS error 10054. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147014842)
Get help: http://help/MSSQL_REPL-2147014842

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199374)
Get help: http://help/MSSQL_REPL-2147199374

The subscription to publication 'yarraman main' could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201019)
Get help: http://help/MSSQL_REPL-2147201019

OS error 10054 is "An existing connection was forcibly closed by the remote host.", which could mean security or network issue. Could this be the case?

Monday, March 26, 2012

merge agent failure after IP changed

Merge agent failure after IP change

I setup merge replication yesterday on Client A and was successful.
Today I changed the gateway and DNS info.; re-ran merge agent and got this
error.
Error messages:
The merge process could not connect to the Publisher 'XXUSSHU18UT:Process'.
Check to ensure that the server is running. (Source: MSSQL_REPL, Error
number: MSSQL_REPL-2147198719)
Get help: http://help/MSSQL_REPL-2147198719
The process could not connect to Publisher 'XXUSSHU18UT'. (Source:
MSSQL_REPL, Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084
Unable to complete login process due to delay in opening server connection
(Source: MSSQLServer, Error number: 0)
Get help: http://help/0
After I changed the IP info. (put back original Gateway IP) the merge agent
ran successful.
What/where do these changes get applied that would alter merge agent from
running?
BTW, I also removed the gateway completely on Cleint A and the merge agent
continued to run successfully again. However I do need the new gateway (the
one that is not working for merge).
thx..
Are you perhaps using an alias on the subscriber? This would explain the IP
address mapping and its relationship to the replication setup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Is this push or pull?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bt7403" <bt7403@.discussions.microsoft.com> wrote in message
news:230FE02E-2D00-44EF-9D1D-A6C3A42C6175@.microsoft.com...
>I setup merge replication yesterday on Client A and was successful.
> Today I changed the gateway and DNS info.; re-ran merge agent and got this
> error.
> Error messages:
> The merge process could not connect to the Publisher
> 'XXUSSHU18UT:Process'.
> Check to ensure that the server is running. (Source: MSSQL_REPL, Error
> number: MSSQL_REPL-2147198719)
> Get help: http://help/MSSQL_REPL-2147198719
> The process could not connect to Publisher 'XXUSSHU18UT'. (Source:
> MSSQL_REPL, Error number: MSSQL_REPL20084)
> Get help: http://help/MSSQL_REPL20084
> Unable to complete login process due to delay in opening server connection
> (Source: MSSQLServer, Error number: 0)
> Get help: http://help/0
> After I changed the IP info. (put back original Gateway IP) the merge
> agent
> ran successful.
> What/where do these changes get applied that would alter merge agent from
> running?
> BTW, I also removed the gateway completely on Cleint A and the merge agent
> continued to run successfully again. However I do need the new gateway
> (the
> one that is not working for merge).
>
> thx..
>
|||I am running a PUSH subscription.
Additionally, after troubleshoot, I found the following:
I had a Gateway, primary, secondary DNS IP's setup before I installed SQL
and configured Replication.
After SQL replication configured and working, had to change Gateway and DNS,
then merge agent would not run. I changed Gateway and DNS back to original
and merge worked; I then removed original Gateway IP (only) and merge worked,
which indicates the DNS change was the problem.
I do not have any alias's setup.
How can I maintain IP changes w/o affecting merge agent?
thx..
"Hilary Cotter" wrote:

> Is this push or pull?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "bt7403" <bt7403@.discussions.microsoft.com> wrote in message
> news:230FE02E-2D00-44EF-9D1D-A6C3A42C6175@.microsoft.com...
>
>

Merge agent error

We have a client on SQL Server 2000 sp2 whose merge replication had the
following error:
The merge process could not update the last received generation.
Does anyone have an idea as to the possible causes?
thanks
It turned out o be query timout invlolving sp_msdelgenzero
"Peter Feakins" wrote:

> We have a client on SQL Server 2000 sp2 whose merge replication had the
> following error:
> The merge process could not update the last received generation.
> Does anyone have an idea as to the possible causes?
> thanks
>

Wednesday, March 21, 2012

MemToLeave and ODBC

We are running SQL Server 2005, SP2, Standard Edition, 64bit.
We are considering an ODBC solution for client reporting, and have been
reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
I was wondering if the queries sent via this ODBC connection, also utilize
MemToLeave, or if those queries use the Buffer Pool?
And then a separate question, not involving ODBC...does an adhoc query
executed from Management Studio utilize Buffer Pool memory, if it needs less
than 8K contiguous memory, or do all adhoc queries in general go directly
against MemToLeave?
As a side note, I have read some from Ken Hendersons blog, threads in this
discussion group, and other posts on the web. I cannot seem to find the
answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still seeking for answers. Please help.
cbrichards wrote:
>We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>We are considering an ODBC solution for client reporting, and have been
>reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>I was wondering if the queries sent via this ODBC connection, also utilize
>MemToLeave, or if those queries use the Buffer Pool?
>And then a separate question, not involving ODBC...does an adhoc query
>executed from Management Studio utilize Buffer Pool memory, if it needs less
>than 8K contiguous memory, or do all adhoc queries in general go directly
>against MemToLeave?
>As a side note, I have read some from Ken Hendersons blog, threads in this
>discussion group, and other posts on the web. I cannot seem to find the
>answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||On a 64bit SQL instance, you should have little to worry about MemToLeave
because you have a huge virtual address space to go about. I might be wrong,
but my understanding is that whether MemToLeave is used depends on factors
such as memory allocation size, not on what database API is being used on the
client side.
Linchi
"cbrichards via SQLMonster.com" wrote:
> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> We are considering an ODBC solution for client reporting, and have been
> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
> I was wondering if the queries sent via this ODBC connection, also utilize
> MemToLeave, or if those queries use the Buffer Pool?
> And then a separate question, not involving ODBC...does an adhoc query
> executed from Management Studio utilize Buffer Pool memory, if it needs less
> than 8K contiguous memory, or do all adhoc queries in general go directly
> against MemToLeave?
> As a side note, I have read some from Ken Hendersons blog, threads in this
> discussion group, and other posts on the web. I cannot seem to find the
> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
RAM (14GB set as Max Server Memory), are you saying that just because I am
running 64bit, that I have a huge virtual address space? Where does this huge
virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
Linchi Shea wrote:
>On a 64bit SQL instance, you should have little to worry about MemToLeave
>because you have a huge virtual address space to go about. I might be wrong,
>but my understanding is that whether MemToLeave is used depends on factors
>such as memory allocation size, not on what database API is being used on the
>client side.
>Linchi
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>[quoted text clipped - 11 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via http://www.sqlmonster.com|||Yes, SQL Server isn't even aware of what API is used by the client application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:76F3D546-8C00-4E8A-81A6-CDA22E1E0193@.microsoft.com...
> On a 64bit SQL instance, you should have little to worry about MemToLeave
> because you have a huge virtual address space to go about. I might be wrong,
> but my understanding is that whether MemToLeave is used depends on factors
> such as memory allocation size, not on what database API is being used on the
> client side.
> Linchi
> "cbrichards via SQLMonster.com" wrote:
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>> We are considering an ODBC solution for client reporting, and have been
>> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>> I was wondering if the queries sent via this ODBC connection, also utilize
>> MemToLeave, or if those queries use the Buffer Pool?
>> And then a separate question, not involving ODBC...does an adhoc query
>> executed from Management Studio utilize Buffer Pool memory, if it needs less
>> than 8K contiguous memory, or do all adhoc queries in general go directly
>> against MemToLeave?
>> As a side note, I have read some from Ken Hendersons blog, threads in this
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>>|||MemToLeave is a virtual memory address space concept. It has nothing to do
with how mch physical memory you have.
Let's forget about performance and physical memory for now. When you write a
program in a high-level programming language, you may feel like you can just
keep allocating data structures. But eventually you'll run into out of memory
error because the compiler/OS knows that there is a limit to the number of
unique virtual memory addresses for a process. If those addresses are all
used up in the process, you are not allowed to allocate any more and it's the
OS' job to keep track of the usage of the virtual memeory addresses.
On a 32-bit system, the number of virtual memory addresses is limited.
Basically there are 2GB for a program to allocate its data structures (the
other 2GB are taken by the kernel). Since the SQL process' virtual address
space may get fragmented after it has been allocating data structures for a
while and fragmentation can get so bad so that it may not be able to find
contiguious virtual addrsses for larger data structures (i.e. larger than
8K), the strategy that SQL Server uses is to pre-allocate a chunk of virutal
addresses when it starts so that this chunk is available regardless of it
snormal allocation activities. This pre-allocated chunk of virtual memory
addresses is MemToLeave.
Now with a 64-bit system, the virutal address space is so large that there
is little to no danger that a SQL instance (primarily its buffer pool) will
ever use up so much of virtual addresses that its process will have no
contiguious virtual addresses left for larger data structures.
Again, this is all related virtual memory addresses, and has nothing to do
with how much physical memory you may have on a particular system. In
reality, of course if you don't have enough physical memory, you risk
swapping things to paging files and your performance will suffer.
Linchi
"cbrichards via SQLMonster.com" wrote:
> So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
> RAM (14GB set as Max Server Memory), are you saying that just because I am
> running 64bit, that I have a huge virtual address space? Where does this huge
> virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
>
> Linchi Shea wrote:
> >On a 64bit SQL instance, you should have little to worry about MemToLeave
> >because you have a huge virtual address space to go about. I might be wrong,
> >but my understanding is that whether MemToLeave is used depends on factors
> >such as memory allocation size, not on what database API is being used on the
> >client side.
> >
> >Linchi
> >
> >> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> >>
> >[quoted text clipped - 11 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via http://www.sqlmonster.com
>|||I am really perplexed at this whole virtual address space with 64bit and I do
not seem to be getting a straight answer. It is probably the way I am
phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
my disks, then if the 14GB is being used by SQL Server, and then another
query comes along, and the 14GB is consumed, is the virtual address space the
2GB left for the operating system, plus the 100GB carved out on the SAN?
Tibor Karaszi wrote:
>Yes, SQL Server isn't even aware of what API is used by the client application.
>> On a 64bit SQL instance, you should have little to worry about MemToLeave
>> because you have a huge virtual address space to go about. I might be wrong,
>[quoted text clipped - 19 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||First of all, virtual address space of a process has nothing to do with how
much physial memory or disk storage you may have on a system. On 32-bit
Windows, the user-mode virtual address space is 2GB (or 3GB with /3GB switch
in the boot.ini). On 64-bit Windows, the virtual address space of a process
is 8TB. These numbers don't change regardless how much RAM or disk storage
you have.
Secondly, the size of the virtual address space is a function of the width
of the address or the number of unique pointer values. So in theory, on
32-bit system, the virtual address space is 2^32, and if each address points
to a byte, that's 4GB. Also in theory, on a 64-bit system, the virtual
address space is 2^64, and if each points to a byte, that's more than 16
exabytes. But actual implementations often impose additional restriction. For
instance, 32-bit Windows gives 2GB to the kernel and leave 2GB to your app to
use. And since 16 exabytes are excessive, currently beyond reach anyway, and
costly to support, only 43 bits are actually used for user-mode virtual
addresses on current x64, and that's ~8TB.
Now, if you really end using this much virtual address space, they (i.e.
virtual memory allocated to represent your data structures) have to be
backed/supported with real stores, which can be physical memory or paging
files.
Linchi
"cbrichards via SQLMonster.com" wrote:
> I am really perplexed at this whole virtual address space with 64bit and I do
> not seem to be getting a straight answer. It is probably the way I am
> phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
> is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
> my disks, then if the 14GB is being used by SQL Server, and then another
> query comes along, and the 14GB is consumed, is the virtual address space the
> 2GB left for the operating system, plus the 100GB carved out on the SAN?
> Tibor Karaszi wrote:
> >Yes, SQL Server isn't even aware of what API is used by the client application.
> >
> >> On a 64bit SQL instance, you should have little to worry about MemToLeave
> >> because you have a huge virtual address space to go about. I might be wrong,
> >[quoted text clipped - 19 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||On Thu, 04 Oct 2007 01:49:25 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I have 16GB RAM, of which 14GB
>is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
>my disks, then if the 14GB is being used by SQL Server, and then another
>query comes along, and the 14GB is consumed, is the virtual address space the
>2GB left for the operating system, plus the 100GB carved out on the SAN?
There are, or so I heard at some time way in the past, computers with
the architecture where memory and disk are mapped with a common
address space, as if each was an extension of the other. That sounds
like what you are asking about. Microsoft SQL Server does not run on
any such architecture.
To oversimplify a bit... Most of SQL Server's memory is used for
caching database pages. If SQL Server needs a page that is already in
cache it is just referenced from that memory location. If it needs a
page that is not in cache it is read into cache. When there is no
room in memory for a new page a page that hasn't been used recently is
overwritten. When a page is updated it will be written to disk -
eventually. (Logs are handled a bit differently and always written
immediately.)
Sorry if I have misunderstood your statement.
Roy Harvey
Beacon Falls, CT

Monday, March 19, 2012

Memory usage in Task Manager

Hallo,

I am a newbie on SQL server and my problem is this:

My SQL server runs with many client queries and after a while I can observe that the meory usage of the SQL server shown in the Windows Task Manager is growing up (e.g. 260 MB !!!).

I checked the online books and found the settings "min/max server memory" which I set to

min = 4 MB
max = 20 MB

by Enterprise manager.

Then I restarted my SQL server, checked the memory settings again by Enterprise manager and started many client queries. The memory usage in Task Manager nevertheless exeeded the 20 MB.

What is my failure? How can I limit the memory usage of SQL server?

Thank you very much for any help...After the change, did you stop and restart SQL?

Friday, February 24, 2012

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/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.
>

Wednesday, March 28, 2012

Merge Control Synchronization Problem

merge client failing

Monday, March 26, 2012

merge agent failure after IP changed

Merge agent failure after IP change

Merge agent error

Wednesday, March 21, 2012

MemToLeave and ODBC

Monday, March 19, 2012

Memory usage in Task Manager

Friday, February 24, 2012

Memory Pressure in MemtoLeave

Memory Pressure in MemtoLeave

Sql Memory Leaks

Blog Archive

About Me