locked
oledb provider for linked analysis server ran out of memory RRS feed

  • Question

  • Hey all
    I am using t-sql openquery() to execute simple queries against linked ssas cubes.
    These queries return only one cell as thier results.
    Both sql and analysis services are 2005 sp3.
    From time to time the oledb provider which is used by the linked server in openquery()
    Throws the following error:"The oledb provider for linked server reported an error:The provider ran out of memory".
    When I created the linked server I chose the oledb provider for analysis services 9.

    If I script this linked server I see that provider string (@provstr) includes "msolap.3" but @provider value is just "msolap"
    (don't know if that matters...).

    I have read here http://support.microsoft.com/kb/937033
    that there was a confirmed memroy leak related to linked server queries ran through openquery().
    However file versions that are included in this fix (listed in the link above) appear to be 9.0.3169.
    This version is earlier then sp3 (9.0.4035), which makes me essume sp3 I am using
    should include this fix.

    Why then am I still getting this error?

    Thanks alot for your attention
    Rea








    Sunday, September 6, 2009 3:39 PM

Answers

  • Hi Rea,

    do you see any other error messages in the SQL ERRORLOG or the Applicaton Log?

    Did you try to increase the MemToLeave area in SQL?

    “Specifies an integer number of megabytes (MB) of memory that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory outside of the memory pool is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 MB.

    Use of this option might help tune memory allocation, but only when physical memory exceeds the configured limit set by the operating system on virtual memory available to applications. Use of this option might be appropriate in large memory configurations in which the memory usage requirements of SQL Server are atypical and the virtual address space of the SQL Server process is totally in use. Incorrect use of this option can lead to conditions under which an instance of SQL Server may not start or may encounter run-time errors.

    Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:

    ·         "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"

    ·         "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"

    These messages might indicate that SQL Server is trying to free parts of the SQL Server memory pool in order to find space for items such as extended stored procedure .dll files or automation objects. In this case, consider increasing the amount of memory reserved by the -g switch.”
    http://msdn.microsoft.com/en-us/library/ms190737.aspx

     

     

    HTH,

    Orsi


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Raymond-Lee Friday, September 18, 2009 9:14 AM
    Monday, September 7, 2009 10:07 AM
    Answerer

All replies

  • Hi Rea,

    do you see any other error messages in the SQL ERRORLOG or the Applicaton Log?

    Did you try to increase the MemToLeave area in SQL?

    “Specifies an integer number of megabytes (MB) of memory that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory outside of the memory pool is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 MB.

    Use of this option might help tune memory allocation, but only when physical memory exceeds the configured limit set by the operating system on virtual memory available to applications. Use of this option might be appropriate in large memory configurations in which the memory usage requirements of SQL Server are atypical and the virtual address space of the SQL Server process is totally in use. Incorrect use of this option can lead to conditions under which an instance of SQL Server may not start or may encounter run-time errors.

    Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:

    ·         "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"

    ·         "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"

    These messages might indicate that SQL Server is trying to free parts of the SQL Server memory pool in order to find space for items such as extended stored procedure .dll files or automation objects. In this case, consider increasing the amount of memory reserved by the -g switch.”
    http://msdn.microsoft.com/en-us/library/ms190737.aspx

     

     

    HTH,

    Orsi


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Raymond-Lee Friday, September 18, 2009 9:14 AM
    Monday, September 7, 2009 10:07 AM
    Answerer
  • Did you ever get this resoloved? I'm having the same exact problem. I installed SQL Server SP3. I'm doing a bunch of selects from Linked Server ( to a sybase database), and eventually I get that stupid error.
    Wednesday, November 4, 2009 2:01 PM
  • I was adviced both here (see above) and by microsoft's support team to increase the MemToLeave area in SQL.
    See orsolyas gal response above.
    Wednesday, November 4, 2009 4:53 PM