none
Select query gives insufficient system memory. RRS feed

  • Question

  • Issue: Msg 701, Level 17, State 123, Line 1
    There is insufficient system memory in resource pool 'default' to run this query.

    Details : Some times we are getting the above issue when we run the select query. Please give us heads up why this is happening, what is the cause  and what could be the resolution.

    SQL VERSION is: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    Memory out of 8GB RAM we set max memory to 5120 MB is set to default, the error is happening here in default instance.

    there is 1 named SQL instance and SSIS, SSRS running on this server.

    Please help me in troubleshooting this and to resolve this.

    Thanks.
    Monday, December 8, 2014 6:01 PM

Answers

  • Details : Some times we are getting the above issue when we run the select query.

    Memory out of 8GB RAM we set max memory to 5120 MB is set to default, the error is happening here in default instance.

    there is 1 named SQL instance and SSIS, SSRS running on this server.

    Hi Venkatesh,

    Thanks for posting SQL Server version but it would be great if you would have posted the select query also. Is it plain select query or with group by and order by.

    The very first line says that there is problem with query considering fact that you have 8 G RAm with multiple instance and that too SSIS,SSRS there

    I would say use proper where clause and limit the result set.

    Are you getting OOM error in SSMS ? This OOM error can as well be SSMS limited memory error


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by venkatesh1985 Thursday, December 11, 2014 7:00 PM
    Monday, December 8, 2014 7:21 PM
    Moderator

All replies

  • Monday, December 8, 2014 6:13 PM
  • I feel the setting of max memory is not right, the max memory setting just restricts SQL Server on it's buffer pool usage. Non-buffer pool components still a parameter for rest of the memory.

    You also told there is a named instance with SSIS, SSRS. Definitely these components consumes extra memory.

    I would recommend to Increase the size of RAM or Try decreasing the MAX Memory size to 3.5 GB.

    --Prashanth


    Monday, December 8, 2014 6:50 PM
    Answerer
  • Details : Some times we are getting the above issue when we run the select query.

    Memory out of 8GB RAM we set max memory to 5120 MB is set to default, the error is happening here in default instance.

    there is 1 named SQL instance and SSIS, SSRS running on this server.

    Hi Venkatesh,

    Thanks for posting SQL Server version but it would be great if you would have posted the select query also. Is it plain select query or with group by and order by.

    The very first line says that there is problem with query considering fact that you have 8 G RAm with multiple instance and that too SSIS,SSRS there

    I would say use proper where clause and limit the result set.

    Are you getting OOM error in SSMS ? This OOM error can as well be SSMS limited memory error


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by venkatesh1985 Thursday, December 11, 2014 7:00 PM
    Monday, December 8, 2014 7:21 PM
    Moderator
  • Venkatesh,

    It would be good of you if you can actually tell what was issue and how you resolved it, it would help other users


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, December 11, 2014 7:57 PM
    Moderator
  • I am agreed with others that need to provide your SQL Statement .

    Have you seen the discussion on below link ?

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bc6250bc-05b7-43d8-b28a-cca4713612ef/there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query?forum=sqldatabaseengine


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Friday, December 12, 2014 8:05 AM