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

    Question

  • I am getting this error repeatedly on SQL Server 2008 on Windows machine when i run a simple query which involves close to ~ 10 million records.
    select sum(a.test) from (select COUNT(*) as test from xf.dbo.sec_mthprc s1 group by gvkey, datadate) a

    Technical Data:
       Sql Server 2008 : 2009-07-29 03:13:16.280 Server       Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
                                 Mar 29 2009 10:11:52
                                 Copyright (c) 1988-2008 Microsoft Corporation
                                 Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)
       Microsoft Windows 2008 Standard Edition x64 with 16 GB RAM and 2 quad-core processors

    Monday, August 03, 2009 3:45 AM

Answers

  • Hi Pranav

    This is Mark Han, Microsoft Support SQL Engineer. I'm glad to assist you with the problem

    According to your description, I understand that when you running the command, the error("Exception of type 'System.OutOfMemoryException' was thrown) will appear.

    in order to resolve the issue, I would like to explain the following
    1please veirfy that the tool you use to run the query is not 32-bit. if the tool is 32-bit, this kind of behavior is expected. Therefore, to resolve the issue, we need to use 64-bit Tool to run the sql command

    2 regarding to the error message, we could know the computer does not have sufficient memory to complete the requested operation. So let's set the max memory for the sql server so that the sql won't take as much momery as it can; and memory might be enough for the OS to complete the operation.

    3 to further diagnose the problem, we also need to check the memory status of the sql and the situation of the performance of the OS when the issue happens. So please help to post the following information
    a) can we always reproduce the issue?
    b) run dbcc memorystatus while the issue happens
    c) check the sql error log to see if there are some memory error logged

    if you have any questions on the above, please let me know. Thanks.

    Regards
    Mark Han
    Wednesday, August 05, 2009 8:13 AM
  • Hi Janathan

    Thank you for the update.

    since only 32-bit SQL Server Management Studio is available, we need to run the query by SQLCMD. The syntax as follows
    c:\> sqlcmd -S"SQLInstanceName" -d"DbName" -i"InputFile.sql" -o"OutputFile"

    Also, we could run the 32-bit SQL Server Management Studio on a 32-bit OS and use the SSMS to connect to the 64-bit instance remotely.

    Besides, about more information, there is an article to share with you:http://support.microsoft.com/?id=906892

    if you have any questions on the above, please let me know.

    Regards
    Mark Han
    Thursday, August 06, 2009 1:46 AM
  • Could you check in your Task Manager the utilization of RAM. See whether your RAM is utilised upto 100%. Also, please mention the capacity of your server in terms of processor and RAM.

    If you have higher RAM - like about 4GB+ and its not being utilised maximum, then I would suggest you to check the 3G switch of your system.

    Microsoft Techie

    3GB doesn't apply to 64 bit Servers which have a 8TB of user VAS exceeding the physical limits of RAM available in a single server currently.

    Hello Mate,

    As this SQL server is 64 bit, you need to make sure that you have min and Max server memory set. Because on 64 bit SQL server can take as much memory as it wants to take. So it is pretty much required to bound it to some limit.

    Can you please send the dbcc memorystatus output of the time when you are running the querry and the execution plan with statistics profile.

    Kind regards,
    Harsh Chawla


    Harsh Chawla

    One of the key things pointed out above is you need to set max server memory on the server.  This is a recommended best practice for all SQL Servers, but it is imperative for 64 bit environments which as I point out above have 8TB of VAS which makes over commitment of memory easy.  With 16GB if RAM I'd start somewhere around 12GB for SQL as a max if this is a dedicated SQL Server that is not running any other applications.  Then monitor the Memory\Available MBytes counter and increase the max server memory up from 12GB slowly making sure that the Available MBytes stays in the 100-150MB range at all times.  In addition to having max server memory set, you also will want to enable the Lock Pages in Memory right in the local security policy for the SQL Service Account.

    http://support.microsoft.com/kb/918483



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, August 04, 2009 2:22 AM
    Moderator

All replies

  • Could you check in your Task Manager the utilization of RAM. See whether your RAM is utilised upto 100%. Also, please mention the capacity of your server in terms of processor and RAM.

    If you have higher RAM - like about 4GB+ and its not being utilised maximum, then I would suggest you to check the 3G switch of your system.

    Microsoft Techie
    Monday, August 03, 2009 10:24 AM
  • Hello Mate,

    As this SQL server is 64 bit, you need to make sure that you have min and Max server memory set. Because on 64 bit SQL server can take as much memory as it wants to take. So it is pretty much required to bound it to some limit.

    Can you please send the dbcc memorystatus output of the time when you are running the querry and the execution plan with statistics profile.

    Kind regards,
    Harsh Chawla


    Harsh Chawla
    Monday, August 03, 2009 8:52 PM
  • Could you check in your Task Manager the utilization of RAM. See whether your RAM is utilised upto 100%. Also, please mention the capacity of your server in terms of processor and RAM.

    If you have higher RAM - like about 4GB+ and its not being utilised maximum, then I would suggest you to check the 3G switch of your system.

    Microsoft Techie

    3GB doesn't apply to 64 bit Servers which have a 8TB of user VAS exceeding the physical limits of RAM available in a single server currently.

    Hello Mate,

    As this SQL server is 64 bit, you need to make sure that you have min and Max server memory set. Because on 64 bit SQL server can take as much memory as it wants to take. So it is pretty much required to bound it to some limit.

    Can you please send the dbcc memorystatus output of the time when you are running the querry and the execution plan with statistics profile.

    Kind regards,
    Harsh Chawla


    Harsh Chawla

    One of the key things pointed out above is you need to set max server memory on the server.  This is a recommended best practice for all SQL Servers, but it is imperative for 64 bit environments which as I point out above have 8TB of VAS which makes over commitment of memory easy.  With 16GB if RAM I'd start somewhere around 12GB for SQL as a max if this is a dedicated SQL Server that is not running any other applications.  Then monitor the Memory\Available MBytes counter and increase the max server memory up from 12GB slowly making sure that the Available MBytes stays in the 100-150MB range at all times.  In addition to having max server memory set, you also will want to enable the Lock Pages in Memory right in the local security policy for the SQL Service Account.

    http://support.microsoft.com/kb/918483



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, August 04, 2009 2:22 AM
    Moderator
  • Hi Pranav

    This is Mark Han, Microsoft Support SQL Engineer. I'm glad to assist you with the problem

    According to your description, I understand that when you running the command, the error("Exception of type 'System.OutOfMemoryException' was thrown) will appear.

    in order to resolve the issue, I would like to explain the following
    1please veirfy that the tool you use to run the query is not 32-bit. if the tool is 32-bit, this kind of behavior is expected. Therefore, to resolve the issue, we need to use 64-bit Tool to run the sql command

    2 regarding to the error message, we could know the computer does not have sufficient memory to complete the requested operation. So let's set the max memory for the sql server so that the sql won't take as much momery as it can; and memory might be enough for the OS to complete the operation.

    3 to further diagnose the problem, we also need to check the memory status of the sql and the situation of the performance of the OS when the issue happens. So please help to post the following information
    a) can we always reproduce the issue?
    b) run dbcc memorystatus while the issue happens
    c) check the sql error log to see if there are some memory error logged

    if you have any questions on the above, please let me know. Thanks.

    Regards
    Mark Han
    Wednesday, August 05, 2009 8:13 AM

  • 1please veirfy that the tool you use to run the query is not 32-bit. if the tool is 32-bit, this kind of behavior is expected. Therefore, to resolve the issue, we need to use 64-bit Tool to run the sql command


    Regards
    Mark Han
    What 64bit tool would you recommend for running the SQL command since the tools provided with SQL Server are only available 32bit?

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, August 05, 2009 1:31 PM
    Moderator
  • Hi Janathan

    Thank you for the update.

    since only 32-bit SQL Server Management Studio is available, we need to run the query by SQLCMD. The syntax as follows
    c:\> sqlcmd -S"SQLInstanceName" -d"DbName" -i"InputFile.sql" -o"OutputFile"

    Also, we could run the 32-bit SQL Server Management Studio on a 32-bit OS and use the SSMS to connect to the 64-bit instance remotely.

    Besides, about more information, there is an article to share with you:http://support.microsoft.com/?id=906892

    if you have any questions on the above, please let me know.

    Regards
    Mark Han
    Thursday, August 06, 2009 1:46 AM
  • I had the same problem. It occures after I open on SSMS a 10 MB SQL script file. The problem resolved after I closed that big file. So I guess the problem is not enuogh RAM on the client machine. 
    Sunday, December 18, 2011 7:18 AM
  • I also had the same error caused by having a large script file open in SSMS.  Once the script file was closed I was able to successfully run the query.
    Thursday, December 22, 2011 7:13 PM
  • I'm getting the same error on a 128 GIG 8 Quad Machine... the Memory is sitting steady at 37.6 GB, the CPU usage is zero when the query runs (24,224,947 records) but somewhere along the line, the network capacity seems to go south and this causes the query failure (I think).


    R, J

    Thursday, July 26, 2012 5:23 PM
  • yes, I'm getting this error and it's definitely a SSMS client error, not a problem on the server.

    In my case, it's a 64-bit workstation w/ 24GB of ram.  Task Manager shows SSMS.exe*32 with 701MB of Private working bytes and there's ~15GB of "available" memory.  I have existing connections up to several servers... running simple queries on any of them return the same message.  I have another instance of SSMS  running that behaves w/o any problems and I've been using ~2 instances of SSMS w/o problem for years.  

    This is SQL 2012 with SP1... which I've found to be the buggiest version of SQL Management studio yet, so this isn't very surprising.  

    Thursday, June 27, 2013 3:11 PM
  • I've been getting this since I switched to SSMS 2012.   Getting sick of it.  I've tried reinstalling SSMS - removed all my Redgate tools, etc...  I now have a clean install, 16GB of ram (x64) only SSMS installed (not even a local SQL) and I'm still getting this.  I get it about 1-2 times a week.  And what's the most frustrating is I usually see it when I'm doing some research query that is going to return 5 rows out of a 100 row table....

    I like SSMS 2012, but I can't work this way. I'm looking at going back to SSMS 2008, aaghhh.   How long before there is a fix?

    p.s.  switching to SQLCMD mode in SSMS did not help.

    Wednesday, July 03, 2013 2:51 PM
  • I am using SQL Server 2012.

    c:\> sqlcmd -S"SQLInstanceName" -d"DbName" -i"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\DBSCript.sql" -o"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\DBName_Log.log"

    It shows the following error,

    sqlcmd: 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\DBSCript.sql' : Invalid filename.

    Kindly help me, where i did mistake..

    Thanks in Advance...

    Regards,

    Seenivasan

    • Edited by vasanvgp Wednesday, August 28, 2013 10:19 AM
    Wednesday, August 28, 2013 10:18 AM
  • Hi Pranav

    This is Mark Han, Microsoft Support SQL Engineer. I'm glad to assist you with the problem

    According to your description, I understand that when you running the command, the error("Exception of type 'System.OutOfMemoryException' was thrown) will appear.

    in order to resolve the issue, I would like to explain the following
    1please veirfy that the tool you use to run the query is not 32-bit. if the tool is 32-bit, this kind of behavior is expected. Therefore, to resolve the issue, we need to use 64-bit Tool to run the sql command

    2 regarding to the error message, we could know the computer does not have sufficient memory to complete the requested operation. So let's set the max memory for the sql server so that the sql won't take as much momery as it can; and memory might be enough for the OS to complete the operation.

    3 to further diagnose the problem, we also need to check the memory status of the sql and the situation of the performance of the OS when the issue happens. So please help to post the following information
    a) can we always reproduce the issue?
    b) run dbcc memorystatus while the issue happens
    c) check the sql error log to see if there are some memory error logged

    if you have any questions on the above, please let me know. Thanks.

    Regards
    Mark Han

    Hi, I am getting that error too. I CANNOT change any server settings - no processor and memory upgrades. Period. How do I find out if my SSMS is 32 bit or 64 bit. It does not mention that on the help. 

    Also, can you tell me if this link is right about 32/64 bit SSMS - http://sivasql.blogspot.com/2011/05/sql-server-management-studio-ssms-is-32.html

    Friday, November 01, 2013 9:51 PM
  • Hi Janathan

    Thank you for the update.

    since only 32-bit SQL Server Management Studio is available, we need to run the query by SQLCMD. The syntax as follows
    c:\> sqlcmd -S"SQLInstanceName" -d"DbName" -i"InputFile.sql" -o"OutputFile"

    Also, we could run the 32-bit SQL Server Management Studio on a 32-bit OS and use the SSMS to connect to the 64-bit instance remotely.

    Besides, about more information, there is an article to share with you:http://support.microsoft.com/?id=906892

    if you have any questions on the above, please let me know.

    Regards
    Mark Han

    I am not so sure if I can use SQLCMD instead. I am trying to run my query inside SSIS. SSIS is on a client machine and SQL Server is on a server box. To run SQLCMD on the server, I have to have access to the server which I don't. So, it looks like SQLCMD is also not an option for me. Now what do I do ?

    Friday, November 01, 2013 9:54 PM
  • I had the same problem. It occures after I open on SSMS a 10 MB SQL script file. The problem resolved after I closed that big file. So I guess the problem is not enuogh RAM on the client machine. 
    LOL ! I had 200MB scripts. People have tried GB scripts too and had this error.
    Friday, November 01, 2013 9:54 PM
  • I also had the same error caused by having a large script file open in SSMS.  Once the script file was closed I was able to successfully run the query.

    Next time, for everyone's benefit, please mention what you mean by "huge". I had a 200mb sql file. Even when the file was not open anywhere else, SSMS could not execute it. So, I am not sure how that worked for you.

    Friday, November 01, 2013 9:56 PM