[SQL Server 2000 ENT] WARNING: Failed to reserve contiguous memory of Size= 6422528
-
Hello,
A beautiful problem to be subjected to this community…
Servers:
- SQL Server 2000 ent. sp4 8.00.2187 cluster active/passive
- Windows server 2003 ent. editon SP1
- 4 CPU (hyperthreading capable)
- 12 gig of RAM
- AWE enabled
- 9.7 gig fixed to SQL Server
- sp_configure (summary) :
allow updates 0 1 0 0
awe enabled 0 1 1 1
default full-text language 0 2147483647 1033 1033
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 9728 9728
max worker threads 32 32767 255 255
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 9728 9728
network packet size (B) 512 32767 4096 4096
priority boost 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
user connections 0 32767 0 0
Therefore, the problem occurs between 6 to 8 days since two months :
2007-07-04 08:12:55.88 spid86 WARNING: Failed to reserve contiguous memory of Size= 6422528.
2007-07-04 08:12:56.25 spid86 Buffer Distribution: Stolen=8221 Free=385 Procedures=114922
Inram=0 Dirty=51548 Kept=0
I/O=0, Latched=221, Other=1069887
2007-07-04 08:12:56.25 spid86 Buffer Counts: Commited=1245184 Target=1245184 Hashed=1121656
InternalReservation=549 ExternalReservation=58 Min Free=256 Visible= 194632
2007-07-04 08:12:56.25 spid86 Procedure Cache: TotalProcs=61677 TotalPages=114922 InUsePages=30265
2007-07-04 08:12:56.25 spid86 Dynamic Memory Manager: Stolen=123143 OS Reserved=2216
OS Committed=2175
OS In Use=2115
Query Plan=114087 Optimizer=0
General=8773
Utilities=12 Connection=2198
2007-07-04 08:12:56.25 spid86 Global Memory Objects: Resource=2915 Locks=136
SQLCache=4850 Replication=2
LockBytes=2 ServerGlobal=26
Xact=51
2007-07-04 08:12:56.25 spid86 Query Memory Manager: Grants=1 Waiting=0 Maximum=118278 Available=118220
2007-07-04 08:12:56.47 spid86 Error: 17803, Severity: 20, State: 12
2007-07-04 08:12:56.47 spid86 Insufficient memory available..
2007-07-04 08:13:05.22 spid84 WARNING: Failed to reserve contiguous memory of Size= 6422528.
2007-07-04 08:13:05.68 spid84 Buffer Distribution: Stolen=9315 Free=2980 Procedures=111524
Inram=0 Dirty=51218 Kept=0
I/O=0, Latched=216, Other=1069931
2007-07-04 08:13:05.68 spid84 Buffer Counts: Commited=1245184 Target=1245184 Hashed=1121365
InternalReservation=579 ExternalReservation=226 Min Free=256 Visible= 194632
2007-07-04 08:13:05.68 spid84 Procedure Cache: TotalProcs=61317 TotalPages=111524 InUsePages=21302
2007-07-04 08:13:05.68 spid84 Dynamic Memory Manager: Stolen=120839 OS Reserved=1264
OS Committed=1242
OS In Use=1072
Query Plan=110746 Optimizer=0
General=8770
Utilities=12 Connection=2195
2007-07-04 08:13:05.68 spid84 Global Memory Objects: Resource=2915 Locks=136
SQLCache=4845 Replication=2
LockBytes=2 ServerGlobal=26
Xact=51
2007-07-04 08:13:05.68 spid84 Query Memory Manager: Grants=2 Waiting=0 Maximum=123534 Available=123308
2007-07-04 08:13:05.82 spid84 Error: 17803, Severity: 20, State: 12
2007-07-04 08:13:05.82 spid84 Insufficient memory available..
2007-07-04 08:13:21.69 spid82 WARNING: Failed to reserve contiguous memory of Size= 6422528.
2007-07-04 08:13:21.96 spid82 Buffer Distribution: Stolen=9319 Free=2228 Procedures=112122
Inram=0 Dirty=51355 Kept=0
I/O=1, Latched=216, Other=1069943
2007-07-04 08:13:21.96 spid82 Buffer Counts: Commited=1245184 Target=1245184 Hashed=1121515
InternalReservation=574 ExternalReservation=910 Min Free=256 Visible= 194632
2007-07-04 08:13:21.96 spid82 Procedure Cache: TotalProcs=61601 TotalPages=112122 InUsePages=21522
2007-07-04 08:13:21.96 spid82 Dynamic Memory Manager: Stolen=121442 OS Reserved=1264
OS Committed=1242
OS In Use=1099
Query Plan=111343 Optimizer=0
General=8792
Utilities=30 Connection=2188
2007-07-04 08:13:21.96 spid82 Global Memory Objects: Resource=2915 Locks=136
SQLCache=4848 Replication=2
LockBytes=2 ServerGlobal=26
Xact=51
2007-07-04 08:13:21.96 spid82 Query Memory Manager: Grants=4 Waiting=0 Maximum=123959 Available=123049
2007-07-04 08:13:22.10 spid82 Error: 17803, Severity: 20, State: 12
2007-07-04 08:13:22.10 spid82 Insufficient memory available..
---------------------
Once this error occurs the front-end application (Remedy Action Request) does not response any more, we must do a manual failover on the database server in order to temporarily correct the problem.
Two Microsoft KB articles related to the error, KB832674 and KB903002. I reject these suggestions for my problem. The first article because no SQL_variant fields is present in the tables.
Does somebody already have this kind of problem?
An option would seem to me interesting to enabled “Reserve physical memory for SQL server” (set working set size), I read that this option can increase the performances if the fixed memory option is activated and SQL Server 2000 is the principal software on the server, which is my case. I also read this option is not recommended if AWE is enabled?!
Thanks in advance for your help or advice.
Mathieu
-
Related my be the post titled ;701 "insufficient System Memory"' at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=344369
SQL = Scarcely Qualifies as a Language
-
Is the /3GB switch set. Can you monitor the memory used by SQL Server using perfmon counters and see at what time this error occurs. I suspect some incorrect setting in memory has been done.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Hi Matt,
I've had a lot of these problems before as well. This is probably one of the more frustrating I've had. As a little background, you are having pressure in the "MemToLeave" memory area. Essentially, there are certain objects in SQL that stored in this area, which has a default of 384 MB. This is split into 2 parts: 128 MB for worker threads and 256 MB for other misc things. Setting the -g will add memory to the 256 MB part. I would say trying -g384 or -g512 would be a good first thing to try as alluded to before. If that doesn't help, there may be no way to alleviate this in a 32 bit environment. This is a limitation of the 4 GB virtual memory limit, not physical memory. That is, throwing more memory at this problem will not help. 64-bit + SQL 2k5 will alleviate this memory pressure. If that isn't an option, balancing the load on more servers may help. I personally hated this problem because I couldn't do much to fix it (I was supporting 3rd party software, too). I hope this helps.
Thanks,
Eric
P.S. If you want to really understand the problem, you NEED "The Guru's Guide to SQL Server Architecture and Internals" by Ken Henderson
-
Hello guys!
I had many problems like this when we decided to change our cluster. Memtoleave since the begining...
This was the second time that I did this change, and finally, I think that is solved. The first time I had to return to the old SQL cluster.
I hope this helps you guys...
The box configuration:
Windows Server 2003 Ent 32 bit Service Pack 2
32 gb RAM
4 quad core
SQL Server 2000 Ent Service Pack 4
Interface SQL X Oracle (using provider MSDAORA)
Issue:
After errorlog message, the linked server stop to work and you have to restart the SQL service:
WARNING: Failed to reserve contiguous memory of Size= 65536.
Procedure Cache: TotalProcs=262 TotalPages=591 InUsePages=266
Buffer Counts: Commited=1152000 Target=1152000 Hashed=287164...
Buffer Distribution: Stolen=4612 Free=859633 Procedures=591...
WARNING: Failed to reserve contiguous memory of Size= 65536.
Query Memory Manager: Grants=0 Waiting=0 Maximum=126667 Available=126667
Global Memory Objects: Resource=985 Locks=3555 ...
Dynamic Memory Manager: Stolen=5203 OS Reserved=928 ...
Query Memory Manager: Grants=0 Waiting=0 Maximum=126667 Available=126667
Global Memory Objects: Resource=985 Locks=3555 ...
Dynamic Memory Manager: Stolen=5214 OS Reserved=928 ...
Procedure Cache: TotalProcs=261 TotalPages=586 InUsePages=263
Buffer Counts: Commited=1152000 Target=1152000 Hashed=287164...
Buffer Distribution: Stolen=4628 Free=859622 Procedures=586...
Solution:
The linked servers must be loaded out of the SQL Server process
1)
Availability of Windows Server 2003 Post-Service Pack 2 COM+ 1.5 Hotfix Rollup Package 12
http://support.microsoft.com/kb/934016
2)
How to run a DLL-based COM object outside the SQL Server process
http://support.microsoft.com/kb/198891
In my case => Modified the object MSDAORA
PS.: I recommend to use the oleview.exe. Don't do this directly on registry.
3)
Linked servers should be loaded out of process.
It can be done by setting the AllowInProcess registry value for the provider to 0 (the AllowInProcess value can be found at HKLM\Software\Microsoft\MSSQLServer(or MSSQL$instance key)\Providers\[ProviderName]).
Explanation:
"MemToLeave is primarily used by non-SQL Server code that happens to be executing within the SQL Server process. The MemToLeave area is memory that is left unallocated and unreserved, primarily for code that is not part of the core SQL Server and therefore does not know how to access memory in the SQL Server buffer pool. Some examples of components that may use this memory include extended stored procedures, OLE Automation/COM objects, linked server OLEDB providers and ODBC drivers, MAPI components used by SQLMail, and thread stacks (one-half MB per thread). This does not just include the .EXE and .DLL binary images for these components; any memory allocated at runtime by the components listed above will also be allocated from the MemToLeave area. Non-SQL Server code makes its memory allocation requests directly from the OS, not from the SQL Server buffer pool. The entire SQL Server buffer pool is reserved at server startup, so any requests for memory made directly from the operating system must be satisfied from the MemToLeave area, which is the only source of unreserved memory in the SQL Server address space."
If somebody has any question, please feel free to ask...
See u!
Tafarelo
http:\\dicasdeumdba.wordpress.com
-
I wish the above could be applied to SQL Server 2005 as well! Is there anything like the AllowInProcess for version 2005? A quick search in the registry provided no results.
Anyway, I am experiencing a similar problem and would like to give my contribution: once the sql server process has reached a certain amount of allocated memory, all connections with external data sources start to fail; actually, clr assemblies stop to load as well.
I'm running MSSQL 2005 Standard Edition ver. 9.00.4035.00 (SP3) on Windows Server 2003 32bit, reserving ~4 GB for the sql service with AWE enabled; the problem arises in a number of cases, including OPENDATASOURCE and OPENQUERY towards Access databases and Excel sheets, BULK INSERTs with linked servers and CLR procedures: pretty much everything that can be classified as an external data source, involving the use of external dlls.
Things work fine until the process reaches a certain amount of allocated memory, which is usually around 4GB but can sometimes be ~1.5GB; unfortunately, restarting the service appears to be my only option at the moment 🙁
Here's what happens sometimes in the middle of a large bulk insert:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "...".
and here's what I get upon launching a CLR procedure:
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.
instead, OPENDATASOURCE and OPENQUERY commands will fail complaining about "null" linked servers...
As usual, restarting the service makes everything alright.
I have tried the -g option before with little success, will try and switch to 64bit as soon as I get the chance.
Thanks for any help you may provide, bye!
-
There are the same registry key on SQL 2005, but this key only appear when you have created a linked server. In your case, you need to create with the MDASQL provider, the registry key probably will be HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MSSQL.1\PROVIDERS\MDASQL\AllowInProcess
Previously, my option was restart the service as well, but the cause was MemtoLeave and only was solved after I move dll MSDAORA to run out of process. There are other Microsoft article that maybe helps you, but refers to move out Extended Stored Procedures (XP_): http://support.microsoft.com/default.aspx?scid=kb;EN-US;243428
Since the last change, when I did this setup, my SQL cluster is working fine, without need to restart. This MemtoLeave problem is actually solved. 😀
-
thanks for your reply! i will try your solution and keep you informed of any progress...
bye
-
Hi Folk,
Thanks for posting, I am passing thru this same issue and would like to know about the LInked Server change, did it affect your application behavior at any point?
I afraid my applications may get impacted after I do this change.
Thanks.
Best Regards,
Marcos Rosa / marcosfac@gmail.com
-
Hello Marcos,
When I was facing the memtoleave problem, my applications that need interface with oracle, always stop working. Acctually, all my SQL instance stopped working and had to be restarted. Just when I performed that configuration, everything started to working fine without issues and service restarts.
This problem affect the SQL Server internal management, in a specific area, but impact the sql service. If you are facing this same problem in the sql 2000, probably this solution will help you. But, you need to be carefull to do these changes, and perform backups of everything you need that you can recovery in case of some mistake.
The behavior of applications was the same, because they just need of the sql service and linked servers available. They are in different tiers, and also, often in different servers.
You can see some screen shoots and explanation in my blog (in portuguese): dicasdeumdba.wordpress.com
Marcos Leandro Rosa (6/11/2012)
Hi Folk,Thanks for posting, I am passing thru this same issue and would like to know about the LInked Server change, did it affect your application behavior at any point?
I afraid my applications may get impacted after I do this change.
Thanks.
-
Thanks for your reply folk.
We are building a new server in order to stress test it. What is happening is that even having the available MPA memory on server, SQL Server is not able to use it, I already have the memory lock options enable on my GPOS also my buffer pool is not getting the whole virtual memory and we configured it to use 6GB , I will try to reduce it to 5GB to increase my MPA and if it does not work, certainly I will change the linked server options.
Just t oclarify, when I do the change, the linked server will be processed at host side, right.
see ya
Best Regards,
Marcos Rosa / marcosfac@gmail.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply