locked
Frequently getting Provider ran out of memory error RRS feed

  • Question

  • Hi All,

    I’m using a Linked server in my application and frequently we are facing the “Provider ran out of memory” error. Linked server is running in SQL Server 2005.

    We have SQL Server 2005\2008 running in our dev server, this problem arises after installing SQL Server 2008.

    Server Configuration:

    Windows Server 2003 R2 SP2

    SQL Server 2005 SP3 and SQL Server 2008 SP1

    32GB RAM and 150 GB free space in drive.

    Note: we are getting this error once in two days. (I used to restart the SQL Server Service to overcome this issue temporarily)

    How to trouble shoot and overcome this error?

    Thanks in advance.


     

    Friday, September 23, 2011 11:03 AM

Answers

  • Check if you are facing the issue as mentioned in the below blog post

    http://blogs.msdn.com/b/psssql/archive/2009/09/22/if-you-use-linked-server-queries-you-need-to-read-this.aspx

    Try to move the Linked server provider out of SQL server process memory.

    Method 1
    Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ProviderName

    Method 2
    Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box.


    Sandeep Dasam

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 4:31 AM
    Friday, September 23, 2011 11:31 AM

All replies

  • Check if you are facing the issue as mentioned in the below blog post

    http://blogs.msdn.com/b/psssql/archive/2009/09/22/if-you-use-linked-server-queries-you-need-to-read-this.aspx

    Try to move the Linked server provider out of SQL server process memory.

    Method 1
    Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ProviderName

    Method 2
    Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box.


    Sandeep Dasam

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 4:31 AM
    Friday, September 23, 2011 11:31 AM
  • Is this 32 or 64 bit?

    Do you have max server memory configured?

    Linked servers use space outside the buffer pool, so it sounds like you're running out of memory for these processes (MemToLeave).  You can try capping max server memory, or look at the -g startup parameter for SQL Server, and try ramping it up a bit...



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Friday, September 23, 2011 11:37 AM