Solved

MSSQLSERVER- Insufficient Memory Available

Posted on 2010-11-08
4
1,072 Views
Last Modified: 2012-05-10
OS : Windows 2003
SQL :SQL 2000 SP4

ERROR.JPG
0
Comment
Question by:Intelops
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 3

Expert Comment

by:thetime
ID: 34083201
0
 

Author Comment

by:Intelops
ID: 34083330
Thanks for your reply, The server is having 16 GB of RAM and AWE is enabled.. the above article didnt help
0
 
LVL 3

Expert Comment

by:thetime
ID: 34083371
The above article refers to virtual memory, not physical memory. AWE allows SQL to utilize more physical memory. There is a table below showing the different -g start up commands depending on your number of databases, try one of them and see if it solves the problem?
0
 
LVL 3

Accepted Solution

by:
thetime earned 250 total points
ID: 34083419
This will also be a possibility:

Aaron Bertrand [SQL Server MVP] - 05 Oct 2008 18:13 GMT
> - upgrade to SQL 2000 Enterprise to SP4 2.00.2039.

2039 is SP4 RTM, which had an issue where RAM above 2GB would not be used.
You should be keeping 1 GB for the OS and background services, so increasing
to 3GB might not be a huge lifesaver, but in addition to following the
advice in the KB article Uri pointed out, you should install the 2040
hotfix.  See the following article for the download:

http://support.microsoft.com/kb/899761/

I had a similar issue on a SQL Server 2005 server with 16GB of RAM.  It took
several months, but I finally convinced PSS that there was a memory leak in
temp tables (well actually, the temp table destruction background process
was far outpaced by the system's temp object construction).  They issued a
hotfix for SQL Server 2005 SP2, but I don't think they have any plans of
back-porting that fix to SQL Server 2000, since SQL Server 2000 is just
about nine years old and is well past its support lifecycle.  So if you
continue to have this issue (and insist on running SQL Server 2000 for a
long time) then I would look to see if you have any (or many) procedures
that create #temp objects.  Make sure you drop the objects when you're done,
that you don't create them repeatedly in a huge loop, and of course check
for potential infinite loops in any while or cursor code...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question