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
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
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.

  • Has there been any unusual usage spikes lately? I had a server that worked fine for years. One month all of our inspectors were hitting the database at the same time because their boss told them they needed their data uploaded by the end of the week. This unusual usage choked the server and I started to get memory errors. It took me a while to figure it out since this wasn't really a problem directly on the server. If the server is being used more than it was in the past, your query may just be acting as the straw that broke the camels back. – Sean Howat Dec 10 '09 at 18:16
  • No, this has always been a pretty heavily used server anyway. The user was able to re-run his proc manually, and it succeeded. At this point, we're just hoping it was some quirk of the individual query, and that we won't see it again... – BradC Dec 17 '09 at 23:36

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.