locked
SSMS Out of Memory Exception RRS feed

  • Question

  • Hi all,

    I am frequently getting the following error when running queries in SSMS.

    An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

    This latest occurred when executing the following query.

    SELECT DATEADD(mm, -12, GETDATE())

    Attached is a screen shot from my resource meter which clearly shows that I still have sufficient memory available, I am running Windows 7 64 bit with 24 GB of RAM (over half of that is available).  So far the only solution I have found is to close SSMS completely and restart the application.

    I am also running Red Gate add-ins for SSMS (SQL Prompt, Source Control and a few others), not sure if these are part of the problem.

    Any help would be appreciated.

    Wednesday, August 6, 2014 12:52 AM

Answers

  • Thanks Kip,

    I had come to the conclusion that the RedGate tools may be the issue.  I have upgraded to the latest release, along with a new workstation, and haven't had the issue for a while, so may they have fixed the problem.

    Monday, March 21, 2016 10:31 PM

All replies

  • Hi TooManyMembers,

    According to your error message, the error will occur when you use Microsoft SQL Server Management Studio (SSMS) to run an SQL query that returns a large amount of data. If SSMS has insufficient memory to allocate for large results, the SQL query will be executed failed. Usually, you need to output the query results as text or to a file, or use the sqlcmd tool to run the SQL queries.

    For more information, see: http://support.microsoft.com/kb/2874903/en-us

    In addition, I recommend you uninstall Red Gate, then run the same SQL query in SSMS, and check if it can run well, and verify if the Red Gate affect the used memory in SSMS.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Wednesday, August 6, 2014 8:22 AM
  • Can you please check Maximum server memory
    In Management Studio right click on server > property > memory.
    Increase Maximum server memory and try your query


    • Edited by ImranKazi Wednesday, August 6, 2014 9:14 AM
    Wednesday, August 6, 2014 9:13 AM
  • Imran

    Changing the Max Server Memory for a server instance is not going to fix the SSMS OOM issue.

    The error is only due to SSMS OOM condition and has nothing to do with SQL Server Service. What Sofiya mentioned is correct. He needs to uninstall all third party addins for SSMS and try again.

    Check this blog - http://troubleshootingsql.com/2010/08/22/system-outofmemoryexception-for-management-studio/

    May be he has opened too many ssms tabs doing huge data retrievals.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    • Edited by Ashwin Menon Wednesday, August 6, 2014 9:19 AM
    • Proposed as answer by Sofiya Li Monday, August 18, 2014 6:22 AM
    Wednesday, August 6, 2014 9:18 AM
  • Thanks Ashwin,
    Ill not it down it. 
    and waiting for ToomanyMembers replay.  
    Wednesday, August 6, 2014 9:23 AM
  • Thanks for your response.

     However, if you look at the query that has caused this error this time you will see that it has a very tiny footprint, it is not a large query and only returns a single value.

    I did have about 4 server connections and a number of query windows open when I started this query, none of which returned a large dataset as can be seen by the amount of RAM consumed by SSMS in the screen shot.  To get this simple query to run I had to close all connections and all query windows.

    Wednesday, August 6, 2014 11:17 PM
  • Are you able to reproduce the issue? If not there might be something which was taking up too much memory in SSMS.

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, August 7, 2014 8:51 AM
  •  However, if you look at the query that has caused this error this time you will see that it has a very tiny footprint, it is not a large query and only returns a single value.

    The stick that broke the camel's back, apparently.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 7, 2014 9:19 PM
  • It's a bug in RedGate - it happens after you do a SQL Search on a database with lots of stuff.  The solution is to restart SSMS and all will be well (no need to uninstall RedGate nor to allocate memory etc.).  Don't know how much "stuff" is "lots", but we've used SQL Search successfully on several databases, but another db (with many, many tables) always has this problem.  So, we avoid using SQL Search there, or open another SSMS to do it, closing it after the search.  Perhaps an update will fix, we have v6.4 (SQL Prompt is at 7.1 at this writing.)

    Kip Bryan

    Monday, March 21, 2016 1:50 PM
  • Thanks Kip,

    I had come to the conclusion that the RedGate tools may be the issue.  I have upgraded to the latest release, along with a new workstation, and haven't had the issue for a while, so may they have fixed the problem.

    Monday, March 21, 2016 10:31 PM