January 14, 2010 at 3:57 am
I have a machine running SQL Server 2005 SP3 on a 32 Bit Machine with 25GB of memory with AWE enabled using about 15GB. I have had several issues with "There is insufficient system memory to run this query." messages but only on a small scale. We upgraded the memory from 25GB to 40GB and the system started producing a ton of these errors. I found several possible problems with memory leaks in SQL Server 2005 SP2 that SP3 addressed. So we upgraded to SP3. Some of the errors have vanished but the ones below from the log file remains to be a problem. Any ideas on what we can do?
Message
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Message
Error: 701, Severity: 17, State: 193.
Message
There is insufficient system memory to run this query.
Message
Memory Manager
VM Reserved = 1960784 KB
VM Committed = 407712 KB
AWE Allocated = 8241152 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
Message
Memory node Id = 0
VM Reserved = 1956624 KB
VM Committed = 403704 KB
AWE Allocated = 8241152 KB
SinglePage Allocator = 1176280 KB
MultiPage Allocator = 283736 KB
Message
MEMORYCLERK_SQLGENERAL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 7944 KB
MultiPage Allocator = 2408 KB
Message
MEMORYCLERK_SQLBUFFERPOOL (Total)
VM Reserved = 1629656 KB
VM Committed = 110808 KB
AWE Allocated = 8241152 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 8728 KB
---------------------------------------
I can give more info if needed.
January 14, 2010 at 4:05 am
I failed to mention the OS of the machine is Windows 2003 Server Enterprise 32 Bit with R2 and the version of SQL Server 2005 is Standard edition with newly installed SP3.
January 14, 2010 at 12:58 pm
Did you try reading this article. It might actually help in your case I see there is a high value for single page allocator from what you have posted.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 14, 2010 at 2:01 pm
It appears the hotfix mentioned in the article is included in SP3 that has been recently installed. But thanks for the reply.
January 15, 2010 at 1:43 am
Have you checked whether Windows is showing all 40GB of RAM. Check and confirm.
January 15, 2010 at 2:08 am
This is strange.
I had exactly the same problem yesterday (is same day as DBA_Help) on 2 servers.
both are on SQL 2005 SP3 (9.0.4207) and have 16 GB ram. They are configured to use 12GB and are using 12GB.
It think it was triggered by a distributed transaction. (it was at he same timeframe)
server 1 :
14/01/2010 08:46:59.050 spid932 Failed allocate pages: FAIL_PAGE_ALLOCATION 2970
server 2 :
14/01/2010 08:46:58.920 spid307 Failed allocate pages: FAIL_PAGE_ALLOCATION 2970
January 15, 2010 at 5:49 am
Did you check the boot.ini for the /3GB switch? It shouldn't be there.
Robbert
January 15, 2010 at 5:55 am
/3gb switch in boot.ini not implemented
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
January 15, 2010 at 7:48 am
This is what is in the boot.ini and no 3GB switch is listed.
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE
January 15, 2010 at 7:56 am
Yes the machine is using the memory. Attached is a snapshot of the task manager with the total memory 40GB and available around 3GB.
I have two instances of SQL one has AWE configured using 24000MB and the other is using 10000MB.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply