locked
Out of Memory exception in SQL 2008 RRS feed

  • Question

  • Hi
        I am running data warehouse stored procs on SQL 2008 and after a while I get this error message
    An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

    If I run teh exact same process on Sql 2005, there is no such message taht comes..
    There is a lot of data and lots ofprocessing going on
    How can I take care of this.
    Thanks
    -Sarah
    • Moved by Jonathan KehayiasMVP Friday, May 1, 2009 1:30 AM Problem sounds like a problem in Management Studio, not with Transact SQL (From:Transact-SQL)
    Friday, May 1, 2009 12:42 AM

Answers

All replies

  • Are these stored procedures returning results back to Management Studio?  This sounds to me like a SSMS exception and not an internal SQL Server exception which would point to a lack of memory on your local machine, and not necessarily a problem with SQL Server itself, perhaps with Management Studio 2008.  If you run the stored procedure from the command line with SQLCMD do you get the same problem?


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 1, 2009 1:00 AM
  • Everything is happening in sql server management stuido. I am running stored procs on SQL SERVER Management studio. How can I diagnose it?
    Friday, May 1, 2009 1:22 AM
  • What are the hardware specs for the machine you are running this on?  To isolate it to SSMS, you can run the stored procedure from sqlcmd:

    sqlcmd Utility

    which is a part of the SQL Tools.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 1, 2009 1:25 AM
  • Here is the exact error

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

    Friday, May 1, 2009 1:28 AM
  • Can you pls me in running the stored prc from sqlcmd, how can I access it from SQL Tools? How should I do sqlcmd Utility

    Windows server standard, service pack 1, 32 bit OS and 4 gb ram

    Thanks for your help..
    Friday, May 1, 2009 1:37 AM
  • I have moved your post to the SQL Sever Tools General Forum because this doesn't sound like a Transact-SQL related problem or question, but more of a problem with the SQL Server Management Studio, or the computer that you are running on. 

    The start to diagnosing the problem is to call the stored procedure using the command line tools, and see if it runs correctly.  That helps isolate the problem to SSMS.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 1, 2009 1:38 AM
  • Can you pls me in running the stored prc from sqlcmd, how can I access it from SQL Tools? How should I do sqlcmd Utility

    Windows server standard, service pack 1, 32 bit OS and 4 gb ram

    Thanks for your help..

    If you are running this on the SQL Sever server machine itself, try running the stored procedure from a desktop or other machine with Management Studio installed on it.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 1, 2009 1:40 AM
  • how can I run the stored proc from a desktop? Yes  this stored proc has been running on a machine that has SQL Server 2005 and this error never came there...

    how can I run it from the cmd prompt

    Start --- Run  cmd--- what should I write in teh cmd window..?

    Thanks for your help
    -Sarah
    Friday, May 1, 2009 1:43 AM
  • Please advise.. From other box with sql server 2005 the proc runs fine from SSMS but not from Sql Server 2008

    Looking forward to your answer.
    Thanks
    -Sarah
    Friday, May 1, 2009 6:44 AM
  • Anyone pls... I need to get it resolved as soon as possible.

    -Thanks
    -Sarah
    Friday, May 1, 2009 6:02 PM
  • Install SQL Managament Studio 2008 on a machine that is not the SQL Server 2008 server, and run the procedure there.  If you still the OOM exception, then you will probably need to file a bug with Microsoft, or open a CSS case to have it looked at.  Running it in SSMS on the SQL Server itself could very well cause it to throw a OOM exception, since the majority of the memory on a SQL Server server is used by the SQL Server Service.  You should by general practice, not be doing work directly on the server unless absolutely required.  Use the tools from your workstation and connect to the server remotely.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 1, 2009 6:05 PM
  • Hi Jonathan
           After doing some more digging into it, I think whats happening is that. I have a Stored proc thats calling this another stored pro about 400 times through a while loop.

    Now in this second stored proc I have about 5 statements like this

    Select min(dtstamp) from dboone.monitor.dbo.monhistory

    elect max(dtstamp) from dboone.monitor.dbo.monhistory

    And about 3 more in the same lines, that means this call to the linked server is happening 400 * 5 times.

    I am thinking taht thats the reason this error message is coming up. What do you think? can that be the reason.

    When I take this call to the linked server out, it runs fine.. But when its back in after about 200 calls, this message comes up.

    Now my question is how can I control this conneciton pool. I need to make these calls to the server and yes I do need the while loop to call this second stored proc.
    Is there a way taht I can open this connection when the stored proc starts and close it before the stored proc ends?

    Please help on this
    Thanks
    -Sarah 

    Tuesday, May 5, 2009 5:26 AM
  • Jonathan et al-
    I had a similar issue with SQL 2008 using SSMS in the same server. Tried connecting from another server with SSMS and still I'm getting the same exception. Next I'll try to work a bit on tuning the procedure I'm running as the error is not showing on the server side, just on the client. There's plenty of memory available for SQL server (around 2 GB) as we have 6 GB max memory and it never reaches the 4 GB.
    What I could see was that at the time of the error, the ssms.exe process in the server is showing the use of a big amount of memory (around 1 GB) and appears to be hanging with the impossibility of running a simple query without getting the memory exception. After closing it and restarting (SSMS, not the SQL Service) it helps and we can run some of the queries but for this particular procedure we still can't go on.

    I found that there was some kind of unresolved bug that was still to be fixed in SP2. Do you know anything about this?

    Any thoughts or help would be great!
    Saturday, October 10, 2009 4:41 AM
  • The OOM exception you are receiving is a .NET exception. This occurs when the result set returned by your query batch is very large and you reach the limits of 32-bit User Virtual Address Space (2GB if no /3GB switch is used). The easiest way to get past this issue is to run the same query/batch from sqlcmd.

    Example: sqlcmd -S<server name> -E -d<database name> -i<input query file> -o<output file>

    SQLCMD link: http://msdn.microsoft.com/en-us/library/ms162773.aspx

    Documenation of similar errors are available @ https://blogs.msdn.com/sqlserverfaq/archive/2009/09/09/oom-error-when-we-access-schema-changes-report-from-ssms.aspx


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: Troubleshooting SQL
    Saturday, October 10, 2009 2:17 PM
  • Amit,

    I am also getting the same error, but here I am running the script where the Ram is 12 GB, 64 bit processor installed with SQL server 2008 Enterprise edition and I restricted the RAM to 9 GB for SQL server process. There is enough Hard disk space in the server. This is was running fine in the SQL server 2005 machine with just 2 GB RAM. Could you please shed some light on this scenario? Note: my process will also be in loop for more than 600 times and will handle Millions of records for each iteration and historical complete time for this task in SQL server 2005 server is nearly 36 Hrs.
    Sunday, November 29, 2009 6:06 AM
  • Using SQLCMD instead of SQL Management Studio solved my problem. I now have a better understanding of why I was having these "Out of memory" errors. When using SQL Management Studio, the "Messages" are saved to memory. The warnings for NULL values when dealing with aggregate "Warning: Null value is eliminated by an aggregate or other SET operation." as well as other messages such as row count were literally draining my memory out. Since SQLCMD is dumping the messages to a file instead of into memory, I don't have these memory issues anymore.

    Another solution to my problem is to disable messages such as "Warning: Null value is eliminated by an aggregate or other SET operation." and the row count but now that I know of SQLCMD, I will be using Management Studio for troubleshooting only.

     

     

    Learning new stuff everyday, thanks!

     

     


    Thursday, May 19, 2011 1:52 PM