My environment is SQL 2008 Standard x86 with SP2 running on Server 2008 Standard, with 4Gb ram and AWE enabled. I also have -g1024 set as a startup parameter.
I am getting the following error in the SQL error log:
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 327680
Then later:
Error: 701, Severity: 17, State: 123.
There is insufficient system memory in resource pool 'internal' to run this query.
Which are repeated until the server eventually stops processing queries.
I strongly suspect that the issue is to do with linked servers as I am linking to a Sybase ASE 15 server using the "Microsoft OLEDB Provider for ODBC Drivers" provider. How can I prove whether or not this is the case and/or troubleshoot this issue?
Thanks.
Edit: Here is the current output for MEMORYCLERK_SQLGENERAL under error conditions..
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2168
MultiPage Allocator 2056
Best Answer
First - is your max server memory set for the SQL Server instance as well? If not, given the limited amount of memory you have, your situation probably warrants it.
Regarding your main question, a few things to check:
So I'm suggesting process-of-elimination here. Also - if you can reproduce the OOMs with specific linked server queries, you can run the memory queries to see before/after and then this will be a pretty good indicator. Also check the linked server provider cases to make sure you aren't encountering known issues. [As an aside since this isn't an Oracle provider - there are cases I know of where you get these messages if the Oracle Linked Server is running in process and taking it out of process helped.]
Also - you may have a legit need for the additional memory, so the long term fix might be to scale up to more memory and x64. You may be able to tweak reserved MTL, and max memory, but this might be memory pressure that can only be resolved by a new architecture and increased memory.