October 25, 2017 at 9:06 am
If I run DBCC MEMORYSTATUS, it fails to finish and throws this error.
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
The SQL Server is running this version of SQL
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
The Windows host hosts about 10 SQL Instances and I see the same error on different instances despite some instances limited to 4GB RAM and others 20GB.
Do I have a real resource issue here or is this some weird SQL bug?
October 25, 2017 at 9:12 am
This error is in your SSMS.
Close and re-open your SSMS.
October 25, 2017 at 9:15 am
This is a problem with Management Studio, I think, not the database engine. Where are you running SSMS? Not on the server, I hope. In order to avoid situations like this, we don't even install it on the server. If you're running it on your desktop, you may need to close some other windows or add more memory.
John
October 25, 2017 at 9:28 am
John Mitchell-245523 - Wednesday, October 25, 2017 9:15 AMThis is a problem with Management Studio, I think, not the database engine. Where are you running SSMS? Not on the server, I hope. In order to avoid situations like this, we don't even install it on the server. If you're running it on your desktop, you may need to close some other windows or add more memory.John
Yes, I was running SSMS on the server. Thanks for the reply.
Will adding more memory help? I read that SSMS is a 32bit app and is limited to 2GB or RAM but the host currently has 4GB free.
October 25, 2017 at 9:29 am
Avoid showing the results in the grid and just show them on text. That might help to reduce memory consumption by SSMS.
October 25, 2017 at 9:34 am
planetmatt - Wednesday, October 25, 2017 9:28 AMJohn Mitchell-245523 - Wednesday, October 25, 2017 9:15 AMThis is a problem with Management Studio, I think, not the database engine. Where are you running SSMS? Not on the server, I hope. In order to avoid situations like this, we don't even install it on the server. If you're running it on your desktop, you may need to close some other windows or add more memory.John
Yes, I was running SSMS on the server. Thanks for the reply.
Will adding more memory help? I read that SSMS is a 32bit app and is limited to 2GB or RAM but the host currently has 4GB free.
Yes, but that 4GB is also needed by the operating system, other users who connect to the server to run SSMS, and any number of other twiddly bits. I don't know whether SSMS was 32-bit back in the days of 2012. I advise you to use the latest version (17.3) and run it on a separate computer - your desktop will do nicely.
John
October 25, 2017 at 9:35 am
Luis Cazares - Wednesday, October 25, 2017 9:29 AMAvoid showing the results in the grid and just show them on text. That might help to reduce memory consumption by SSMS.
i was running a very large script with Dynamic SQL. It looped through a bunch of tables and for each table generated a Dynamic Merge query, I was outputting the SQL itself as a Debug check that the SQL was well formed if it threw an error. Weirdly, the text output in the form of these debug messages is far far larger than the actual result-set shown which is just a few hundred rows.
Could the large amount of text output cause the out of memory error rather than the grid output?
October 25, 2017 at 10:03 am
planetmatt - Wednesday, October 25, 2017 9:35 AMLuis Cazares - Wednesday, October 25, 2017 9:29 AMAvoid showing the results in the grid and just show them on text. That might help to reduce memory consumption by SSMS.i was running a very large script with Dynamic SQL. It looped through a bunch of tables and for each table generated a Dynamic Merge query, I was outputting the SQL itself as a Debug check that the SQL was well formed if it threw an error. Weirdly, the text output in the form of these debug messages is far far larger than the actual result-set shown which is just a few hundred rows.
Could the large amount of text output cause the out of memory error rather than the grid output?
Yes. I run into that occasionally.
October 25, 2017 at 10:06 am
planetmatt - Wednesday, October 25, 2017 9:35 AMLuis Cazares - Wednesday, October 25, 2017 9:29 AMAvoid showing the results in the grid and just show them on text. That might help to reduce memory consumption by SSMS.i was running a very large script with Dynamic SQL. It looped through a bunch of tables and for each table generated a Dynamic Merge query, I was outputting the SQL itself as a Debug check that the SQL was well formed if it threw an error. Weirdly, the text output in the form of these debug messages is far far larger than the actual result-set shown which is just a few hundred rows.
Could the large amount of text output cause the out of memory error rather than the grid output?
It could. SSMS is not designed to display a lot of data, so you shouldn't force it to do so.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply