Solved

MSSQLSERVER- Insufficient Memory Available

Posted on 2010-11-08
4
1,057 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
Comment Utility
0
 

Author Comment

by:Intelops
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now