0

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
0

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.

18
  • Max memory is set to the default of 2147483647. Should I set it to 3x1024? I have had a look at DBCC MEMORYSTATUS but don't know where to look for issues to do with linked servers. We have to use 32bit as 64bit Sybase drivers are not available to us. – Craig Mar 18 '11 at 10:12
  • Save some room for the Operating System too - but yes in this situation you'll want to cap the max memory. The "correct" max memory is really dependent on what else is running, and if you are experience memory pressure, capping may not be a long term solution (reducing buffer pool size means less data in cache), but it is something you should try. Regarding DBCC MEMORYSTATUS - I recommended this so you could rule out other non-buffer pool consumers. More of a process of elimination than a direct way to identify this as a linked server issue. You can check in sys.dm_os_memory_clerks too. – Joe Sack Mar 18 '11 at 11:31
  • And set the max memory with the following in mind: non-buffer pool MTL memory + OS memory + max server buffer pool memory – Joe Sack Mar 18 '11 at 11:35
  • Okay, I am going to go with MTL=1024, Max server memory=3584, leaving 512 for the OS (it does nothing but SQL anyway). Re: DBCC MEMORYSTATUS and other memory status queries, I am unable to use it to rule out anything as I don't understand what it is telling me - I am not a DBA. – Craig Mar 18 '11 at 16:37
  • For an overview of DBCC MEMORYSTATUS, check out: How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005 support.microsoft.com/default.aspx?scid=kb;EN-US;907877 – Joe Sack Mar 18 '11 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.