"Failed to reserve contiguous memory" error killed .net applications
Recently I am working on a old SQL2000 instance which is running 2 OLTP applications on a 4GB Ram. Everything was happy around that instance till some new .net deployment. After the new code went in live, end users intermediately started getting weird errors from their browser reporting typical .net stack dump. Unfortunately the new deployment was a vital part of the application which can not be removed. Hence backend analysis became a necessity and I was summoned.
Very first thing I observed in SQL log is series of errors message throughout the day which complains about memory like below:
Buffer Distribution: Stolen=<> Free=<> Procedures=<>
WARNING: Failed to reserve contiguous memory of Size= <>.
Dynamic Memory Manager: Stolen=<> OS Reserved=<>
Procedure Cache: TotalProcs=<> TotalPages=<> InUsePages=<>
Buffer Counts: Commited=<> Target=<> Hashed=<>
Insufficient memory available..
Error: 17803, Severity: 20, State: 12
WARNING: Failed to reserve contiguous memory of Size= <>.
Dynamic Memory Manager: Stolen=<> OS Reserved=<>
Procedure Cache: TotalProcs=<> TotalPages=<> InUsePages=<>
Buffer Counts: Commited=<> Target=<> Hashed=<>
Insufficient memory available..
Error: 17803, Severity: 20, State: 12
Query Memory Manager: Grants=<> Waiting=<> Maximum=<> Available=<>
Global Memory Objects: Resource=<> Locks=<>
Global Memory Objects: Resource=<> Locks=<>
Also end users experience error when ever the above messages arrive.
Now Error: 17803, Severity: 20, State: 12 in SQL 2000, is described in technet as below :
This error is a generic message indicating an out-of-memory condition somewhere within the SQL Server memory space. This condition could be from non-SQL Server code running within the SQL Server process, such as extended stored procedures or MDAC providers, or it could be from something happening within SQL Server itself.
So this clearly concludes it is a memory issue and when it is becoming severe, application is failing. So I added /3GB switch in 2003 EE OS and bumped up SQL memory use to 3GB fixed (I know this is not a best practice but now SQL Server is at least using around 2.8GB). Also from trace, I noted there are many TCP/IP packets are send which are more than 8060 bytes in size. So I requested developers to add “Packet Size=4096” parameter to the connection string to ensure SQL does not have to battle with big packets.
This made applications stable. However the final recommendation was to migrate the application to SQL 2005 or 2008 and to a bigger box. (Which I will only do J)
So my personal lesson was “there is a silver line in every issue”. In this case, this issue will at last made a migration possible for our rusty instance.
Comments
1) /3GB switch in boot.ini and PacketSize=4096
or
2) migration "to SQL 2005 or 2008 and to a bigger box"
3) or something else?
I think /3GB switch seems doesn't help with 1Gb of memory installed. So we have PacketSize=4096 (as Microsoft recommends) or migration to SQL 2005/2008. Or may be SP2 for WS2003 can help?
Thank you for your comment. In my case adding /3GB and fixing packet size to 4096 at webconfig fixed the issue.
However in your case adding /3GB will not do any difference as you have 1GB RAM. SP2 on Win2K3 also will not do much difference too (though you should upgrade anyway). So if you are positive that the issues are from memory crunch in SQL Server (in that case most likely you are getting error message as per the original post), you will need to either reduce resource demand from the server (by moving some databases to another instance) or increase memory by adding physical RAM.
Also moving to SQL 2008/5 is highly recommended but unless you resolve the actually problem (which is most likely memory crunch in this case), you may not see any notable improvement on your present issue.
Please let me know if you need any more information. You can email me at info@consultdba.com if you prefer.
Thanks again for useful info and gracious help offering. I've forgot about other databases on server and didn't tell you that this server is also domain controller and file server for 10-30 client computers. So the insufficient memory could be the cause.
I think, firstly, we should investigate background if we are using PacketSize greater than 4096 bytes in our applications and as fast remediation add -g384 command-line option to SQL Server startup parameters to increase MemToLeave area as suggested in KB271624 http://support.microsoft.com/kb/271624/en-us and SQLServerCentral forum http://www.sqlservercentral.com/Forums/Topic385018-5-1.aspx
If this doesn't help we'll have to do something with server memory such as adding more, moving or deleting from server some databases that are not used. And afterwards we move it to SQL2008 in next few months.
Any comments on our plan are appreciated. And do you know the way to detect PacketSize>4096 easier than to look at Integer Data column of Existing Connections event in SQL Server Profiler 2000?
Thank you for your reply. Running a server as SQL Server, DC, File server is not a good idea. Apart from performance trouble, you are inheriting other potential issues (like security risks etc) that can be a challenge. I will strongly recommend having different servers for different vital roles in your organization if you are using it for mission critical production environment.
Now to discuss on your strategy to add –g parameter and limiting packet size, you can try this. If you can increase some memory to your SQL Server process, it may decrease the symptom. But from what I understood in your case, you need to move for a permanent solution (at least adding memory) as soon as possible.
Using profiler is one of the easiest and reliable methods to measure packet size. If you need any advice to configure the profile, please let me know.
Your help greatly appreciated. I'm fully agree with you that SQL Server and DC role should not be merged. But it is about our client servers and databases, not our. They have the only server (2xXeon 3GHz CPU, 1Gb RAM and one 160Gb RAID1 on two 160 Gb SATA disks) functioning as DC, SQL Server and fileserver. And we can only give them our recommendations which we are considering now.
We shall try both -g parameter and look at PacketSize. I think, in our situation running scripted commands in Query Analyzer for determining PacketSize could be done more easier than doing Profiler setup on client side.
Next thing, we should try is telling them about increasing memory or separating SQL Server or DC to another server-like computer.
Furthermore, we are looking forward to migrate to SQL2008.
Thank you for your note and I am glad to help. If you face any further issue, do not hesitate to revert back.