40

I'm using Microsoft SQL Server 2012 and trying to run a simple query against it within Management Studio. I'm getting the following error (in SSMS, running on the server):

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

The system has 24GB of RAM installed but looking in task manager the sqlservr.exe process is only using 2.9GB.

Is there a setting somewhere that is restricting its RAM usage?

0

6 Answers 6

41

This error indicates that Management Studio is running out of memory, not the SQL Server service. Even if you installed 64 bit SQL Server, the SQL Server Management Studio executable is a 32 bit application.

This is likely caused by the size of the result set that you are returning to Management Studio. Are you executing something like SELECT * FROM really_big_table? See http://support.microsoft.com/kb/2874903 for more.

2
  • I feel like I get this error w/o ever returning a large result set. Once I get this error, I get it even after I close all query windows, then open up a new one and execute: "SELECT 1". Bouncing SSMS is a pain, you have to save all your open queries to a file and it takes a long time to start.
    – Ivan
    Commented Oct 22, 2021 at 21:50
  • @Ivan have you tried deleting some of the tmpXXXX.tmp files in your TEMP folder? I found that it's not neccessarily a memory issue, but running out of available temp files, that will produce this, in that case rather unintuitive, error message. Leaving .tmp files behind is a problem afflicting many applications, including Microsoft's.
    – Tom Lint
    Commented Dec 6, 2022 at 9:48
19

Mike is right that the error message you're receiving is from the Management Studio application itself, and not from SQL Server. It is the memory on your local workstation that has been exhausted, likely due to trying to pull 16 billion rows into the client application (rendering that much data in a grid is quite expensive in terms of memory, so try to limit your queries using TOP etc. - I don't know what practical thing(s) you could possibly do with enough data to use up all of your local memory anyway).

But I do want to address another problem: using Task Manager to assess the amount of memory SQL Server is using. Don't do this; it's a bold-faced liar. Copying from this answer (your question is two-fold, so I can't really close it as a duplicate):


You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using. Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs:

SELECT object_name, cntr_value 
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';

You could save that as a query shortcut in Tools > Options > Environment > Keyboard > Query Shortcuts, and get accurate results in a query window much faster than getting inaccurate results from Task Manager.

You can also check for memory pressure (and whether you can do anything about it) using these queries:

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

-- SQL Server 2012:
SELECT physical_memory_kb FROM sys.dm_os_sys_info;

-- Prior versions:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;

EXEC sp_configure 'max server memory';
1
  • The above commands are for new versions of SQL. For SQL 2008 R2 servers then the commands there are slightly different. Ill add another post to this lower down for those servers as follow up comments do not format well enough to put here Commented Nov 16, 2016 at 10:29
15

It was the same case with me. My SQL Server Management Studio was open for couple of days. I restarted it and it got solved.

0
9

I found turning off IntelliSense helped. I also recommend checking any add-ins you have (things like RedGate tools and ApexSQL also exacerbated the issue for me).

This issue plagued me for days now and to be honest it's quite weak of Microsoft. They should really have 64 bit tool sets, since we are dealing with big data these days, 64 bit servers, and desktop environments.

1
  • 1
    In my case RedGate SQL Prompt was fighting with SSMS built in intellisense. Turning off SSMS intellisense made SQL Prompt work much better too.
    – TTT
    Commented Jan 26, 2017 at 16:45
1

For SQL 2008 R2 the memory query commands (from Aaron Bertrand's post) are as follows

SELECT object_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)';

SELECT object_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

-- SQL Server 2012:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;

-- Prior versions:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;

Also note that the command

EXEC sp_configure 'max server memory';

may not work unless you have advanced options enabled. e.g. do this first

EXEC sp_configure 'show advanced options',1
RECONFIGURE
0
1

Please note that there is a Microsoft connect issue for this as being an SSMS memory leak. They think they resolved it in v16.5

See the issue on Connect.microsoft.com by clicking here

If you install the latest and still have the problem then please vote it up to get re-opened.

SSMS v16.5 here

Connect issue indicating memory leak fixed in 16.5 here