16

I am running Windows 10 Pro 64 bit on a dev box that has multiple monitors, 16 gigs DDR4 RAM, 4 Ghz I7, GTX 970. I run SQL Management Studio with SQL Server 2016 Developer Edition along with VS 2015 Enterprise Update 3.

Yesterday I upgraded Sql Management Studio 2016 to 13.0.15700.28 and it was like a poison pill for my machine. Now after an hour or two it will throw an out of memory except:

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

Now this is sometimes typical if you are doing very large return sets of over a few million rows. NOT if you are doing

Select Top 10 * from SmallObject

I was doing some new table and procedure creation for new objects to an existing development system. And this just occurs out of the blue for no rhyme or reason. It also appears to be a partial blocking error for SSMS as it now freezes the system and attempts to bring up a connection dialog window like I am first starting SSMS and attempting to connect to a datasource. Thus far it goes to a crawl until I kill it from task manager. I am also running Redgate's SQL Prompt 7.2.0.241. Things I have tried:

  1. Attempted to keep tabs under five at a time and close them as I am done.
  2. Do not break off the tabs from one screen to another.
  3. Turn off Redgate and see if it is the culprit
  4. Check memory use as I go

I know it blew up last night as I came into work and SSMS let me know it had crashed. This may be an MS bug but there may be a bug in Redgate or some other config I have so I thought it best to ask SO and see what others have seen. This build of SSMS is as of 8/15/2016 so it is very new.

Two errors from stack traces of Application Event Logs: Event 1026

Application: ssms.exe Framework Version: v4.0.30319 Description: The process was terminated due to an unhandled exception. Exception Info:

System.ComponentModel.Win32Exception at System.Windows.Forms.NativeWindow.CreateHandle(System.Windows.Forms.CreateParams) at System.Windows.Forms.Control.CreateHandle() at System.Windows.Forms.ComboBox.CreateHandle() at System.Windows.Forms.Control.CreateControl(Boolean) at System.Windows.Forms.Control.CreateControl(Boolean) at System.Windows.Forms.Control.CreateControl(Boolean) at System.Windows.Forms.Control.CreateControl(Boolean) at System.Windows.Forms.Control.CreateControl(Boolean) at System.Windows.Forms.Control.CreateControl() at System.Windows.Forms.Control.WmShowWindow(System.Windows.Forms.Message ByRef) at System.Windows.Forms.Control.WndProc(System.Windows.Forms.Message ByRef) at System.Windows.Forms.ScrollableControl.WndProc(System.Windows.Forms.Message ByRef) at System.Windows.Forms.Form.WmShowWindow(System.Windows.Forms.Message ByRef) at System.Windows.Forms.Form.WndProc(System.Windows.Forms.Message ByRef) at System.Windows.Forms.Control+ControlNativeWindow.OnMessage(System.Windows.Forms.Message ByRef) at System.Windows.Forms.Control+ControlNativeWindow.WndProc(System.Windows.Forms.Message ByRef) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr, Int32, IntPtr, IntPtr)

The other one was an event log 1002 error 'Application Hang' and has no real meaningful help that I can see:

Ssms.exe 2015.130.15700.28 68ac 01d1f98d17a32d16 4294967295 C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe 62a64950-658b-11e6-a2c8-f832e4a07fda

54006F00700020006C006500760065006C002000770069006E0064006F0077002000690073002000690064006C00650000000000

Updated 8-23-2016:

Still get this error from time to time:

The program Ssms.exe version 2015.130.15700.28 stopped interacting with Windows and was closed. To see if more information about the problem is available, check the problem history in the Security and Maintenance control panel. Process ID: 35f8 Start Time: 01d1fca7e48da2da Termination Time: 4294967295 Application Path: C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe Report Id: 4e8b6ab9-693f-11e6-a2cb-f832e4a07fda Faulting package full name:
Faulting package-relative application ID:

Clearly this is an issue for people other than me as I am getting votes on it here: https://connect.microsoft.com/SQLServer/feedback/details/3062914/system-outofmemoryexception-thrown-by-even-small-selects-randomly-now

If you have this happen to you or know a potential fix, please let me know. At this point if I have to do heavy SQL work coming up I am thinking of downgrading at this point. Again I am on Windows 10 64 bit machine and this only happened after upgrading to the most recent SSMS build.

Update 8-24-2016

MS appears to acknowledge this bug now. If you have this happen to you PLEASE go to this link and upvote: https://connect.microsoft.com/SQLServer/feedback/details/3074856

Update 8-31-2016

Latest from MS on exception:

Posted by Microsoft on 8/29/2016 at 10:21 AM turns out there's a thread leak in a utility class. The number of threads leaked will be proportional to the number of registered servers you have, among other things. A fix is coming in the next release

I downgraded as doing work got more important than figuring out what was blowing up. Downgrade for me is working fine now. I gave MS SQL dumps so hopefully they can get a new build in the coming weeks. If you are curious I am on version 13.0.15600.2 and stable thus far as I downgraded two days ago.

DForck42
  • 19,789
  • 13
  • 59
  • 84
djangojazz
  • 14,131
  • 10
  • 56
  • 94
  • Where is the rest of the exception? Where did you find that exception? The text includes the call stack which will show you who actually threw that error. Rest assured that this isn't due to SSMS as others have the same version. Did you check for strange error messages in Event Viewer ? – Panagiotis Kanavos Aug 19 '16 at 15:47
  • I run this SSMS version, VS 2015 Update 3 *and* VS 15 Preview *and* SQL Server 2014 *and* 2016, for ETL/reporting workloads but have no memory issues. If you suspect a plugin issue, just disable all SSMS plugins and check if that fixes the problem – Panagiotis Kanavos Aug 19 '16 at 15:50
  • Finally, OutOfMemoryException doesn't mean you run out of RAM. It's also thrown when memory is so fragmented that .NET can't find a contiguous are large enough eg to grow a large list – Panagiotis Kanavos Aug 19 '16 at 15:53
  • @PanagiotisKanavos: Thanks I will try to look at the stack trace in event logs. I did not know on the fragmentation effect, that is good to know too. To the best of my knowledge the stack is not shown in SSMS, but the event logs should be a good place to look at. – djangojazz Aug 19 '16 at 16:15
  • 1
    Still an issue and turning off suggestions on SQL Prompt does not work. Tried Beta version of SQL Prompt as well. Updated answer – djangojazz Aug 23 '16 at 15:47
  • 2
    2015.130.15700.28 is truly a retrograde step. Like the OP, exceptions are thrown after a couple of hours of less than intensive work. I'd strongly advise **against** upgrading this time around. – SAinCA Aug 31 '16 at 19:35
  • Still an issue in 13.0.16100.1 – karlis Dec 27 '16 at 15:42

3 Answers3

7

I had the same problem. I just close and reopen the SQL Server. was able to over come the error.

An error occurred while executing batch. Error message is: 
Exception of type 'System.OutOfMemoryException' was thrown.
Kapila Perera
  • 837
  • 1
  • 11
  • 24
4

Seems like they fixed the issue with the latest release: SSMS 16.4.1

5. Fixed an issue where an Out Of Memory Exceptions were being thrown.(Microsoft Connect item #3062914)(Microsoft Connect item #3074856)

Nate H
  • 76
  • 6
  • Let me give it a day or so and then I will give you the answer as I saw it myself this morning and downloaded it. – djangojazz Sep 27 '16 at 22:43
  • I get it less frequently since the update, however, I still have to cycle it every couple days because I've "run out of memory". – Nate H Jan 17 '17 at 15:22
  • 1
    v17.1 rarely shows me System.OutOfMemoryException. But locks up frequently anyways. 1 out of 7 times it will show me the out-of-memory exception. Locks up about 4 times a day for me. Bit-9 / Carbon Black Antivirus? Interested if it happens to others without this antivirus. – TamusJRoyce Jul 18 '17 at 19:55
  • 3
    v17.2 and it happens frequently. – TamusJRoyce Aug 22 '17 at 14:22
  • @TamusJRoyce we also have Bit-9. Does your SSMS take forever to start? Ours at our company takes somewhere up to 5 minutes to start. Which makes having to close it down often even the more frustrating. – SpaceGhost440 Jul 30 '18 at 19:18
  • It was pretty quick for us. My guess is there might be a 9-bit rule either causing issues. Or might speed things up. We had a double hopp issue, too. It does access the file system, at least for editing reports strangely. But I am lucky I no longer have to deal with that anymore. But it does not suprise me. – TamusJRoyce Jul 30 '18 at 22:59
1

This happens due to cache memory or unused temp files.

Try deleting Temp file present on %temp% location. If still problem is there restart SSMS.

Abhas Bhoi
  • 349
  • 3
  • 3
  • Up voted, I have no idea why this answer was down voted because this is one of the solution which can work. Especially, when you are using third party tool like Red gate. – Zerotoinfinity Oct 02 '19 at 15:12