Home / server / Questions / 85658
In Process
BradC
Asked: 2009-11-18 12:10:10 +0800 CST

Memory errors on SQL 2000 with SP4?

  • 772

I have a SQL 2000 server with a DTS package that is crashing with insufficient memory errors, where we've never had an issue before.

The DTS package is returning the error "There is insufficient system memory to run this query." The SQL error log shows stuff like: (trimmed duplicate lines for simplicity)

BPool::Map: no remappable address found.
Buffer Distribution:  Stolen=198908 Free=779 Procedures=349 
                      Inram=0 Dirty=10291 Kept=0
Buffer Counts:  Commited=917376 Target=917376 Hashed=717340 
                InternalReservation=174 ExternalReservation=24 
                Min Free=1024 Visible= 199856
Procedure Cache:  TotalProcs=17 TotalPages=349 InUsePages=340
Dynamic Memory Manager:  Stolen=164278 OS Reserved=1048 
                OS Committed=1026 OS In Use=1024
                Query Plan=416 Optimizer=141466
                General=16874
Buffer Counts:  Commited=917376 Target=917376 Hashed=717340
                Utilities=5840 Connection=206
                InternalReservation=174 ExternalReservation=24 
                Min Free=1024 Visible= 199856
Procedure Cache:  TotalProcs=17 TotalPages=349 InUsePages=340
                Utilities=5840 Connection=206
Global Memory Objects:  Resource=1272 Locks=279 
                SQLCache=52 Replication=2
                LockBytes=2 ServerGlobal=23
                Xact=35
Query Memory Manager:  Grants=1 Waiting=0 Maximum=35238 Available=270

Can someone help me interpret/decipher some of these memory numbers?

This is a dedicated Windows2003 server running SQL2000 Enterprise edition, build 8.00.2282 (SP4). It has a total of 8GB of RAM. The SQL instance is configured with Min Memory = 0, Max Memory = 7167. AWE is enabled.

I've found a ton of articles that seem kinda related:

  • KB 838459 - But we're already on SP4, and this isn't a reindex.
  • KB 815114 - Seems relevant, as our query does have a LOT of tables in the join, more than half of them with a LEFT OUTER, but as I said, we're already on SP4.
  • KB 831999 - Ditto. Already on SP4.

I admit this is a big hairy query, but we've run the identical query for years without trouble, and even if the query isn't really optimal, it shouldn't crash the server, or fail to execute, right?

Any ideas? Should we try the trace flag 3940 mentioned in kb838459, even though the scenario isn't exactly the same?

Yes, we've been encouraging them to upgrade to SQL2008 64-bit, but that's a while off.

sql-server

1 Answers

  1. As I posted in my comment to the question, we ran into this problem when one of our SQL servers was receiving more than the normal amount of usage. By increasing the page file we were able to supply a bit more resources to the users during this time period.

    This was not our permanent fix as after this point we were able to reevaluate the performance needs of the machine and gave it a few small upgrades. I wouldn't recommend using this solution to large ongoing problems but it was a good enough band aid for us.

    • 1