October 20, 2007 at 9:24 am
The following memory error captured in our 32 bit SQL server 2005 server,
Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query
MEMORYBROKER_FOR_RESERVE
Allocations = 762
Rate = 0
Target Allocations = 148554
Future Allocations = 33074
....
.........
.........
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536
The following is the server details
OS - Windows server 2003 Enterprise edition with SP1 (32 bit)
Physical RAM - 15.8GB
SQL server details
SQL server version : SQL server 2005
Service pack : SP1
Min memory configuration : 0 MB
Max memory configuration : 10240MB
I have checked the boot.ini file and found \PAE switch has been enabled to use more than 4GB of memory with \fast detect switch. The sql server is enabled with AWE to use more memory. I have checked the virtual memory paging file on C: drive and found 4096MB of memory is allocated.
I guess this is related to virtual memory allocation problem due to insufficient memory. Please help me to identify the exact memory parameter which is causing the problem and how to resolve this memory error.
BR,
Parthi
October 22, 2007 at 6:07 am
Edit: Never mind, I'm talking garbage
Since you have AWE enabled, that's not the issue.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2007 at 2:33 am
Gail, I think you need to review what Microsoft says about the /3Gb and /PAE boot.ini switches...
/PAE activates the Windows addressing extentions that allow appilcations to address more than 4GB memory. This feature can only be used by programs that are PAE-aware, such as SQL Server. However, SQL Server needs the AWE Enabled option set to use PAE memory.
SQL can only use PAE memory for database buffer pools. All other memory must fit below the 4GB line. SQL leaves some memory below the 4GB line unused via the -g startup switch. -g has a default value if it is not specified. Some appilcations will use memory outside of SQL, so it may be the problem reported with Service Broker could be cured by specifying a value for -g greater than the default.
Ultimately, it is likely the server will be memory-constrained when running in 32-bit mode. If the hardware is 64-bit compatible, it may be worthwhile rebuilding the server with 64-bit Windows and 64-bit SQL, as with 64-bit all of the address range can be used for external application memory.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 26, 2007 at 10:41 am
First you want to determine where the memory is being used. I ran into this with 2005 RTM/SP1 on x64. The issue in my case was the procedure cache was taking all the memory on the server.
select * from sys.dm_os_memory_clerks
where type = 'CACHESTORE_SQLCP'
if this is not the case you can expand on the query to see where the memory is being utilized.
If this is the bucket that is using the memory, I would first upgrade to SP2 which has a fix for the procedure cache memory util. if it is not possible to get to SP2 let me know and I can provide some info on how we worked around the issue, and dig up some of the KB articles.
Jim
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply