• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

SQL 2000: There is insufficient system memory to run this query.

When my SQL Server 2000 EE starts up, about 85% of the way through starting databases it throws:

Buffer Distribution:  Stolen=18575 Free=3085931 Procedures=56
Procedure Cache:  TotalProcs=23 TotalPages=56 InUsePages=17
Dynamic Memory Manager:  Stolen=18631 OS Reserved=1424
Global Memory Objects:  Resource=15027 Locks=59
Buffer Counts:  Commited=3328000 Target=3328000 Hashed=223438
Query Memory Manager:  Grants=0 Waiting=0 Maximum=317270 Available=317270
Error: 701, Severity: 17, State: 101
There is insufficient system memory to run this query..

I have a Windows 2008 x64 box with 32GB RAM and SQL 2000 x32 EE.  SQL 2000 has AWE enabled, the service has permissions to lock pages in memory, 8.00.2040 is running, virtual memory is set to 32GB.  Memory in SQL 2000 is set to min 18GB, max 26GB.  Task manager shows 93% of physical memory is used when SQL Server is fully started.  Using the -g512 startup parameter.

This server's purpose is to be a remote backup server.  

We ship backups to it and restore them in standby (meaning they are in read-only mode).  After restoring a good amount of databases (maybe 85% of our databases) we get the insufficient memory error.  If I restart SQL server it will start up the databases but get about 85% of the way in and start throwing the insufficient memory error again.

Any thoughts on how to resolve this?
0
SteveMBO
Asked:
SteveMBO
  • 9
  • 5
  • 2
1 Solution
 
SteveMBOAuthor Commented:
Bumped up virtual memory from System Managed (32GB) to 96GB with no change in the problem.
0
 
QPRCommented:
anything else running when you try to start sql?
0
 
QPRCommented:
shot in the dark....
you don't copy/restore your model database do you?
The 2 servers are the same - level 80 compat mode?
Have you made alterations to your model db on either server?
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
SteveMBOAuthor Commented:
Howdy, I'm going to be out of town till Monday... so the accumulated suggestions will be tried then and I'll post all the results.
0
 
SteveMBOAuthor Commented:
To answer some questions:

Usually I have another small instance of SQL 2008 running on the same box, but I have disabled that and the issue is still occurring.

I have copied and restored the model DB but I don't see how that would make a difference.

Both the source and destination servers are the same SQL 2000 EE.
0
 
QPRCommented:
Are the 2 builds identical?
SELECT   SERVERPROPERTY('productversion') as productversion
 ,SERVERPROPERTY('productlevel') as productlevel
 ,SERVERPROPERTY('edition') as edition

If not you may have problems moving system databases about.
I assume you've modified/added to model and that's why you are restoring it elsewhere?
0
 
QPRCommented:
Have you tried without the -g parameter?
Recommendation is that you only use this if you see entries in the log about freeing up memory
0
 
SteveMBOAuthor Commented:
I updated my script to not move system databases.  The only thing I want to restore is the customer databases which is what it is doing now.  

I started without the -g parameter than ran into this article: http://support.microsoft.com/kb/316749

I have about 6000 databases on this machine.  Modifying the -g parameter from a small value to 4096 doesn't seem to affect the point to where it starts throwing the insufficient memory errors when starting up the databases (it always throws the error after loading about 85% of the databases).  

There are about 6 different SQL servers that produce the backups and they all get restored on this one server.  It doesn't seem to matter which SQL server produced the backup, only that after loading around 85% of the databases it just can't load any more and gives the insufficient memory error.

0
 
SteveMBOAuthor Commented:
What is the best way to profile the SQL Server memory to determine what kind of memory it is running out of?

I feel this issue won't be solved until I can determine the specific area that is having issues.
0
 
QPRCommented:
If system DBs are no longer involved then my comment is redunadant.
I've never restored more than 1 DB in any given session let alone 6000 so wouldn't have a clue on any piotential pitfalls associated with that.
Out of curiosity, how do you do this?
restore DB XYZ from disk.....
go
restore DB ABC from disk....
go
etc

?
0
 
SteveMBOAuthor Commented:
I wrote a Powershell script that looks in a folder filled with backups then goes through each backup and restores it.  It issues one RESTORE DB XYZ FROM DISK for each backup file.  

SQL Server is not really designed to have lots of small databases.  You end up having to write a lot of your own tools because the built-in ones are designed from the 'one large database' paradigm.
0
 
Anthony PerkinsCommented:
Actually, this is where Red-Gate's SQL Backup comes in handly.  No need for scripting.
0
 
SteveMBOAuthor Commented:
We moved away from Red-Gate because it turned out to be totally unreliable for us.  It would backup a database but then throw all kinds of cryptic errors when you tried to restore it... intermittently.  There was also a host of other problems we had.  I try to keep things as plain as possible now.  Native restore, native backup.

But that is beyond the scope of this thread.
0
 
SteveMBOAuthor Commented:
At this point I am looking more for an answer to this question:

What is the best way to profile the SQL Server memory to determine what kind of memory it is running out of?

I feel this issue won't be solved until I can determine the specific area that is having issues.
0
 
SteveMBOAuthor Commented:
To resolve this issue I installed a second instance of SQL and restored half the databases onto that instance and the other half onto the original instance.  Both instances can start with no problem.
0
 
Anthony PerkinsCommented:
>>We moved away from Red-Gate because it turned out to be totally unreliable for us.<<
That has not been our experience.  We have used it for the last 5 years to backup over 400 databases and it has always restored flawlessly.

But you are right that is a totally different thread...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now