SQL 2000: There is insufficient system memory to run this query.
Posted on 2010-01-07
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?