locked
Memory error: Allocation failure : The paging file is too small for this operation to complete RRS feed

  • Question

  • All,
    I am getting the following error while processing the cube on 64-bit operating system(Windows Server 2003 sp2 64 bit with SSAS 2005(64 bit)

    Since this is 64 bit machine we have 8 GB RAM... i dont know why SSAS is not able to use the 8GB RAM...

    Complete Error description:

    Memory error: Allocation failure : The paging file is too small for this operation to complete. . Errors in the OLAP storage engine: An error occurred while processing the 'Fact Table Historical X' partition of the 'Fact Table X' measure group for the 'TEST cube from the AS database.

    Monday, December 28, 2009 8:23 PM

Answers

  • Available Memory :8GB
    Max SQL server Memory:3GB

     

    -       I’m pretty sure you need to limit the maximum amount of memory used by the SQL Server relational engine. So I think your setting is appropriate.

     

    AS Low Memory limit: 50%(4GB)
    AS Total Memory limit: 37.5%( 3GB )

     

    No, that’s not right. I don’t think you need to modify the default setting of SSAS. You just need to limit the memory of SQL not SSAS. SSAS have a pattern of use very different from SQL Server, and paging memory to disk is important for SSAS. Note: TotalMemoryLimit is based on the current available physical RAM and that's one reason why we should use percentage. So if you change it to 37.5, the memory is not 8*37.5%, in fact it's (the current available physical RAM)*37.5% can be used for SSAS. Of cause, SSAS may request more memory even if more than that, but at this time, it will affect the cleaner threads. So:

    1)    Do not change the default setting, or at least keep to TotalMemoryLimit large than LowMemorylimit. And making the gap between these two properties' values too small is not a good idea. Low 60 and total 80.

    2)    If you still have memory pressure issue, increase paging memory.

     

    If you want to know the actual value that a percentage setting (TotalMemoryLimit/ LowMemorylimit) represents is, you can look at the MSAS Memory / Memory Limit High KB and MSAS Memory / Memory Limit Low KB performance counter.

     

    More information, you can refer to:

    http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

     

    Regards,

    Raymond

    • Marked as answer by KiranNJ Thursday, December 31, 2009 2:59 PM
    Thursday, December 31, 2009 2:22 AM
  • Hi,

    I think you have a memory pressure issue. You need to consider increase the both virtual memory and physical memory.

     

    “Since this is 64 bit machine we have 8 GB RAM... i dont know why SSAS is not able to use the 8GB RAM...”


    Note: Did you install other instances on the same machine? If so, 8 GB RAM is not only for SSAS. For example, if you install SQL Server and SSAS on the same machine, then:

    SQL max server memory + SSAS Total Memory Limit + Windows (I would count at least 2GB for the OS) <= 8GB. But the default max memory for SQL is 2147483647MB (All).

    At this time, you need to decrease the MAX memory allocated to SQL and allow SSAS more.

     

    In SSMS, connect to SQL engine, right click the server, click properties, and switch to memory tab.

    Another similar thread:

    http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/40e7737a-4f20-41d3-8f95-ad5e9ee21986

     

    Regards,

    Raymond

    • Marked as answer by KiranNJ Wednesday, December 30, 2009 4:44 PM
    Wednesday, December 30, 2009 5:38 AM

All replies

  • Hi,

    I think you have a memory pressure issue. You need to consider increase the both virtual memory and physical memory.

     

    “Since this is 64 bit machine we have 8 GB RAM... i dont know why SSAS is not able to use the 8GB RAM...”


    Note: Did you install other instances on the same machine? If so, 8 GB RAM is not only for SSAS. For example, if you install SQL Server and SSAS on the same machine, then:

    SQL max server memory + SSAS Total Memory Limit + Windows (I would count at least 2GB for the OS) <= 8GB. But the default max memory for SQL is 2147483647MB (All).

    At this time, you need to decrease the MAX memory allocated to SQL and allow SSAS more.

     

    In SSMS, connect to SQL engine, right click the server, click properties, and switch to memory tab.

    Another similar thread:

    http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/40e7737a-4f20-41d3-8f95-ad5e9ee21986

     

    Regards,

    Raymond

    • Marked as answer by KiranNJ Wednesday, December 30, 2009 4:44 PM
    Wednesday, December 30, 2009 5:38 AM
  • Raymond,
    Thanks for the suggestions.

    I am able to process the cube now.

    I used your hints and made the following changes...

    Available Memory :8GB
    Max SQL server Memory:3GB

    AS Low Memory limit: 50%(4GB)
    AS Total Memory limit: 37.5%( 3GB )


    This setting seems to work for now..

    Is this ideal? should i change the settings?


    Please comment
    Wednesday, December 30, 2009 4:44 PM
  • Available Memory :8GB
    Max SQL server Memory:3GB

     

    -       I’m pretty sure you need to limit the maximum amount of memory used by the SQL Server relational engine. So I think your setting is appropriate.

     

    AS Low Memory limit: 50%(4GB)
    AS Total Memory limit: 37.5%( 3GB )

     

    No, that’s not right. I don’t think you need to modify the default setting of SSAS. You just need to limit the memory of SQL not SSAS. SSAS have a pattern of use very different from SQL Server, and paging memory to disk is important for SSAS. Note: TotalMemoryLimit is based on the current available physical RAM and that's one reason why we should use percentage. So if you change it to 37.5, the memory is not 8*37.5%, in fact it's (the current available physical RAM)*37.5% can be used for SSAS. Of cause, SSAS may request more memory even if more than that, but at this time, it will affect the cleaner threads. So:

    1)    Do not change the default setting, or at least keep to TotalMemoryLimit large than LowMemorylimit. And making the gap between these two properties' values too small is not a good idea. Low 60 and total 80.

    2)    If you still have memory pressure issue, increase paging memory.

     

    If you want to know the actual value that a percentage setting (TotalMemoryLimit/ LowMemorylimit) represents is, you can look at the MSAS Memory / Memory Limit High KB and MSAS Memory / Memory Limit Low KB performance counter.

     

    More information, you can refer to:

    http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

     

    Regards,

    Raymond

    • Marked as answer by KiranNJ Thursday, December 31, 2009 2:59 PM
    Thursday, December 31, 2009 2:22 AM
  • problem fixed here : https://youtu.be/Oh6dga-Oy10
    Saturday, July 4, 2020 8:20 AM