0

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.

Improve this question
2
  • 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. Commented Dec 10, 2009 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... Commented Dec 17, 2009 at 23:36

1 Answer 1

Reset to default
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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.