[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MSSQLSERVER- Insufficient Memory Available

Posted on 2010-11-08
4
Medium Priority
?
1,074 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 1000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

650 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