SteveMBO
asked on
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?
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?
anything else running when you try to start sql?
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?
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?
ASKER
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.
ASKER
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.
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.
Are the 2 builds identical?
SELECT SERVERPROPERTY('productver
,SERVERPROPERTY('productle
,SERVERPROPERTY('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?
SELECT SERVERPROPERTY('productver
,SERVERPROPERTY('productle
,SERVERPROPERTY('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?
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
Recommendation is that you only use this if you see entries in the log about freeing up memory
ASKER
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.
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.
ASKER
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.
I feel this issue won't be solved until I can determine the specific area that is having issues.
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
?
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
?
ASKER
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.
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.
Actually, this is where Red-Gate's SQL Backup comes in handly. No need for scripting.
ASKER
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.
But that is beyond the scope of this thread.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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...
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...
ASKER