Error 701-There is insufficient system memory to run this query

Hi All,
I'm getting this error on a system with AWE enabled and 32GB of memory. I'm sure I can use some windows performance counters and DMVs to determine which SQL component/cache is short of memory, but am not sure which counters/DMVs. Any help would be greatly appreciated...
Thanks,
BigEd

As you have 32G memory , i think it is supposed no bottleneck in memory, but it may be configuration problem.

• AWE requires the database engine to run under a Windows account with Lock pages in memory permission.

So, be sure that the Windows account under which the database engine is running have "Lock pages in memory permission".

• Be sure that you set the min server memory and max server memory options, which enforce upper and lower limits on SQL Server memory utilization.

• Enter the following switches in the boot line of the boot.ini file, and reboot the server:

/PAE
Don't set /3GB switch(remove it if you did)

btw, AWE is not necessary on 64-bit versions of SQL Server 2005.

Hi all,
As moh_hassan20 said, please do all the things.
I was also having the simmilar environment then I got to figured out this error comes out when we call the CLR functions.
Below solved my problem, there are many patches microsoft have introduced for the Insufficient memory error.
My current SP level is cumulative update package 8, I used to get the Insufficent memory error.
I read through the internet and included the startup parameter -g512 and that resolved my problem. i.e default is 256.
Thanks,
Ganesh

Thanks all for the suggestions. I have confirmed that the 32GB is available at the OS level and SQL can see ~28GB of it. I believe the SQL memory shortage is internal to SQL, but I don't know the way to figure out what is using the memory within SQL and what thresholds I am hitting.
I also was looking into the -g boot switch, but would first like to confirm with a counter/DMV that the default of 256 isn't enough.

It looks like you are using 32-bit server. First of all using AWE will help only data/index pages, it won't help you with connectcion memory, log cache, plan cache etc.
Secondly, we don't know which service pack you are running. The following KBA says 701-insufficient memory error is bug and resolved in SP1
Please check http://support.microsoft.com/kb/912439
Regarding your 256MB question, that is MEM to LEAVE area used for greater than 8KB allocations. Memory allocated for com+ objects, extended procs etc. is allocated from mem to leave area. Its value for SQL Server 2000 is 384MB. I don't remember value for 2005 on top of my head. Our friend google can help to find out more details about mem-to-leave area of sql server that would surely help you understand this concept to take decision whether you need -g switch.
HTH
Saurabh