Solved

MSSQLSERVER- Insufficient Memory Available

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

ERROR.JPG
0
Comment
Question by:Intelops
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

810 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