"There is insufficient system memory to run this query."

Question

Post reply

"There is insufficient system memory to run this query."

Florian Reischl

SSC-Dedicated

Points: 37301
More actions
October 28, 2009 at 5:36 am

#212628
Hi!
We have problems with our SQL Server 2005. We get the error message "There is insufficient system memory to run this query" several times per day. Whenever this happens almost all connected processes die.
Since I already searched the forum history, some information for you:
- Service Pack 3 is installed (current version: 9.00.4035.00)
- sqlservr is configured for parameter "... -g1024"
- We do not run any resource governor (it's SQL Server 2005)
Some information about the environment
Box
HP DL 380 G5
CPU: 2x Xeon Quad-Core E5440 2.83 GHz
RAM: 32GB (PAE enabled)
OS: Windows Server 2003 Enterprise x86
SQL: SQL Server 2005 Standard x86
Databases
14 databases up to 240 GB. The main OLTP database is a old school EAV design (legacy) with 180 GB.
Is there any possibility to get the statement/batch which causes this error message?
Any suggestions would be helpful!
If you need any further information, please let me know.
Thanks a bunch
Flo

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 1

perhaps if you implement a 24x7 server-side trace you will be able to capture the offending code or batch:

http://www.mssqltips.com/tip.asp?tip=1841

thanks

SQL_EXPAT

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi AJ

Thanks for the really useful link. I'll definitely share this with our DBAs!

In this special case (I forgot to say this, sorry...) I already have trace files which have been active as the error occurred. But there are no dramatical statements within. Some INSERTs into larger tables and some INSERTs of binary data (PDFs) but nothing really special.

Do you know any special profiler event I could monitor?

Do you know if this error can happen if there are too many INSERT/DELETE/UPDATE statements within one transaction? Some of our import processes into the EAV database can create up to 500,000 statements for one import file (in one transaction).

Thanks

Flo

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 3

it can be a combination of issues. Perhaps check what your tempdb utilisation is too.

We get your error ocasionally on some of our servers where we do very intensive Service Broker queue processing.

thanks

SQL_EXPAT

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi

We don't use the service broker in our scenario. (Could any other activities implicit cause this?)

Tempdb is stored on an own disk (only shared with the "distribution" database for replication). Tempdb has size of 11GB (data-file) and 6GB (log-file) but both files are almost empty. There are more than 70GB free disk space.

Probably the combination of tempdb and distribution-db on same disk is not the best combination but I don't think this could cause the error, could it?

Greets

Flo

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 5

In fact I have a server where this is happening right now. Same error as you:

.

A time out occurred while waiting for memory resources to execute the query

Resource Monitor (0xaa0) Worker 0x000000008000E1C0 appears to be non-yielding on Node 0. Memory freed: 0 KB. Approx CPU Used: kernel 0 ms, user 15 ms, Interval: 64998.

There is insufficient memory available in the buffer pool.

There is insufficient system memory to run this query.

.

Its very hard to troubleshoot. There are also dump files in the \log directory but they dont provide any meaningful info.

Counters: Buffer Cache hit ratio is avrg 93% / Target Server Memory & Total Sever memory are equal indicating all is OK but all is clearly not OK.

I wonder if there could be something wrong with the physical RAM.

thanks

SQL_EXPAT

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Just happened 14th time today...

I noticed (from error-log) that almost all memory caches at zero KB except the MEMORYCLERK_SQLBUFFERPOOL. I'm not sure if this might be the problem.

I attached a small part of the log file. Probably you can see more...

The hint for the physical memory sounds good. I'll speak with our DBAs to plan a switch to the replication server and make a deep hardware check.

Thanks

Flo

Silverfox SSC-Insane Points: 23150 More actions · Answer 7

Sometimes memory issues are not down to available physical memory but virtual, you have a lot of memory and you are already using the -g switch, i would be tempted to increase this value, to possibily 3 times what you have it now and see if the problem persists.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Flo,

You indicated that this is an x86 version (32-bit) of SQL Server 2005 and that PAE is enabled on the OS, is AWE enabled for SQL Server 2005?

Is the server dedicated to SQL Server 2005?

What are your min and max memory set to for SQL Server 2005?

You should be sure to leave 2GB to 4GB for the OS.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 9

I think, we can forget the hardware problems (spoke with our DBA). We just had a larger maintenance some weeks ago. Whenever we have a maintenance we switch to the replication server (due to 24/7). Since this time we had the same problems with our replication server.

@Silverfox:

Thanks for the tip to increase the value for the -g switch, I'll discuss this with our DBA.

Greets

Flo

Silverfox SSC-Insane Points: 23150 More actions · Answer 10

Florian Reischl (10/28/2009)
I think, we can forget the hardware problems (spoke with our DBA). We just had a larger maintenance some weeks ago. Whenever we have a maintenance we switch to the replication server (due to 24/7). Since this time we had the same problems with our replication server.
@Silverfox:
Thanks for the tip to increase the value for the -g switch, I'll discuss this with our DBA.
Greets
Flo

No worries Flo, increases in the -g switch have resolved quite a few memory issues in the past for various clients. I know that you have probably increased this from the default value, but it sounds like a good bet considering your configuration.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Wesley Brown SSChampion Points: 12045 More actions · Answer 11

I didn't see anyone mention

http://blog.sqlauthority.com/2007/03/27/sql-server-fix-error-701-there-is-insufficient-system-memory-to-run-this-query/

I've found setting your min/max memory size fixed this on the two 2005 servers I was having the same issue on. It also went away when we moved to 64 bit on another server. We still set the min/max memory size and use the new log pages in memory option to prevent swapping sql server's memory out to the page file. I also have the page file set to 1.5x the size of the physical memory installed on the server so other apps that can swap out have room to do so.

cheers,

Wes

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 12

Hi Lynn!

Thanks for your help.

is AWE enabled for SQL Server 2005?

Yes, AWE is enabled for SQL Server.

Is the server dedicated to SQL Server 2005?

Yes the server is a physical and dedicated box for the SQL Server.

What are your min and max memory set to for SQL Server 2005?

Min: 0 MB

Max: 24576 MB

So there are about 6GB saved for system.

Greets

Flo

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 13

No worries Flo, increases in the -g switch have resolved quite a few memory issues in the past for various clients. I know that you have probably increased this from the default value, but it sounds like a good bet considering your configuration.

I'll definitely try this with our DBA. We just can't do this at the moment. We've got a 24/7 uptime (well, except this error...), currently there's way to much load to stop any service.

Hopefully we can try this this evening.

Thanks!

Flo

Silverfox SSC-Insane Points: 23150 More actions · Answer 14

@wes

hmm, interesting I have seen the link before but it isnt a 100% fix, it doesnt work in all scenarios.

neither does fixing the min and max. all depends you might be lucky.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]