none
SSRS error- There is insufficient system memory in resource pool 'default' to run the query.

    Question

  • Hi all,

    While running the SSRS report  I have got the below error.

    There is insufficient system memory in resource pool 'default' to run the query

    The report was running fine till now. Couldn't comprehend what was the reason.

    I have searched in the web and forums, but none is addressed with SSRS.

    Requesting you to help.

    Best Regards

    Anupama


    Friday, June 08, 2018 5:56 AM

All replies

  • Is this on preview or while running from server?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 08, 2018 6:47 AM
  • Hi Visakh,

    Yes in preview and also while running in the report manager.

    Friday, June 08, 2018 7:13 AM
  • Hi Visakh,

    Yes in preview and also while running in the report manager.

    see if this helps?

    http://askkrishna.blogspot.com/2016/06/sql-server-error-msg-701-level-17-state.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 08, 2018 7:16 AM
  • Hi Visakh,

    The query runs fine in SSMS. The problem happens to be only in SSRS. The link talks about the problem in SSMS I think.

    Thank you

    Anupama


    Friday, June 08, 2018 8:56 AM
  • The query runs fine in SSMS. The problem happens to be only in SSRS. The link talks about the problem in SSMS I think.

    Per se, there is no difference between SSMS and SSRS. However, there are some quirks in SQL Server and SSMS, which can have the effect that you get difference cache entries when running in SSMS and SSRS, and thereby different query plans. But it could just as well have been the other way round: that the query had executed fine in SSRS and you had gotten this error in SSMS.

    There is an article on my web site that discusses why this can happen in more details and also attempts to give tips to resolve such problems.
    http://www.sommarskog.se/query-plan-mysteries.html
    Note that I don't discuss this particular error message in my article. The message is just a token of a less successful query plan.

    Sunday, June 10, 2018 2:26 PM
  • Do you have resource governor configured on the SQL Server the SSRS report points to? If so, do you have other resource pools with the 'minimum memory' value configured? If yes, then those pools, at any given point of time, retain that much minimum amount of memory. So the "shareable" portion is reduced.

    As an example, let's say you have 3 pools beside 'default' and each has a minimum memory set to 10%, hence 100 - (3x10)=70% is what is shareable among pools and 30% is reserved for as the minimum for the three pools. So ensure that default pool has sufficient min and max memory configured. 

    That said, check the below post. It talks about a possible way to fix this error.

    http://henkvandervalk.com/sql2014ee-insufficient-system-memory-message

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Sunday, June 10, 2018 4:52 PM
  • Hi Mohsin_A_Khan,

    Thank you for the response, but the resource governor is not configured in the SQL server. And the error occurs only for this particular report.

    Thankyou

    Anupama

    Tuesday, June 12, 2018 7:14 AM
  • Hello Erland,

    Thank you for your response. I don't actually found any information related to this error.

    Thank you

    Anuapama

    Tuesday, June 12, 2018 7:15 AM
  • Thank you for your response. I don't actually found any information related to this error.

    Yes, as I pointed out, my article does not discuss this error specifically. The reason you get this error in the SSRS and not in SSMS is that you have different query plans, and why you get is something I discuss in my article. The error message is only a symptom of the underlying problem: that your query is prone to produce a poor query plan. And this is what you should focus on, not the out-of-memory error.

    Tuesday, June 12, 2018 10:30 AM
  • Hi Mohsin_A_Khan,

    Thank you for the response, but the resource governor is not configured in the SQL server. And the error occurs only for this particular report.

    Thankyou

    Anupama

    Since this seems to happen intermittently, I echo Erland's comment. It could be due to a bad execution plan. You might want to capture the execution when the report runs from SSRS vs when executed from SSMS. 

    Flush the cache (Caution: this would force recompilation of queries the next time they run) and run the report again and see the plan if it is changed. If the report runs a stored procedure, try recompiling and see. (sp_recompile).


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, June 12, 2018 3:47 PM