November 4, 2016 at 8:22 am
Hello all,
I am looking for validation and help on this issue, as it's driving me absolutely nuts...
I am still encountering the error 'An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.' on small query sets, sometimes even the first run.
I am currently running the latest release version of Microsoft SQL Server Management Studio
Microsoft SQL Server Management Studio13.0.16000.28
Microsoft Analysis Services Client Tools13.0.1700.441
Microsoft Data Access Components (MDAC)6.1.7601.17514
Microsoft MSXML3.0 4.0 6.0
Microsoft Internet Explorer9.11.9600.18499
Microsoft .NET Framework4.0.30319.42000
Operating System6.1.7601
I approached the SQL CAT team at PASS Summit last week in Seattle regarding this issue, and was effectively told it was my fault and either (A) I had too many sessions open and\or (B) that the data sets being returned were too large or too numerous and therefore ran the system out of memory.
Personally, I was floored by this explanation, as I know for a fact that in a recent release this issue was noted as resolved, and I refuse to accept that the data set being returned is at fault, as the result set of the so called "offending query" in this instance returns a single small row of values, on basic JOINs. I only have two query sessions opened at this occurrence, and I have had it occur on the very first SELECT * query I ran for the workday.
I pushed back, but was met with a create a CONNECT ticket option.
Has anyone else encountered this since the so-called fix was applied a few versions ago? Is there a way I can resolve this myself or at least alleviate the pain?
Thanks ahead of time,
Ken Jackson
Senior Database Analyst at PDMI
November 4, 2016 at 8:36 am
Without a way to reproduce the issue, it would be difficult for anyone to be able to help solve it, but... if you can reproduce this, at least specify the tables, data, and queries, and if someone else can then reproduce the error, they can at least claim reproduction of said error and post that to your Connect item...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 4, 2016 at 8:57 am
Sadly, due to HIPAA constraints, I can't provide the actual data I am querying against in this case, and the inconsistent nature of the error also doesn't help my case.
I've been able to reproduce it on several machines, and several databases in my environment over the past few weeks and months, and the presence of an open CONNECT ticket gives me some validation that this is occurring elsewhere to other users.
I understand that's not much to work off of, I suppose my goal was to find out if anyone else had encountered the same issue or similar in their own environments.
This is the connect ticket that I found that was already open on this issue.
https://connect.microsoft.com/SQLServer/feedback/details/3074856
November 4, 2016 at 12:02 pm
Yes, I've encountered that exact error using SSMS 2008 and SSMS 2012. It's normally when I try to copy large data sets from SSMS to paste elsewhere. It doesn't matter if I'm copying one large column or many smaller columns, but the number of rows seems to be the driver of the error.
Since we're still on SQL 2012, I have no experience with later versions of SSMS.
November 4, 2016 at 3:01 pm
Ed Wagner (11/4/2016)
Yes, I've encountered that exact error using SSMS 2008 and SSMS 2012. It's normally when I try to copy large data sets from SSMS to paste elsewhere. It doesn't matter if I'm copying one large column or many smaller columns, but the number of rows seems to be the driver of the error.Since we're still on SQL 2012, I have no experience with later versions of SSMS.
If that's the problem, then yes, it's the data quantity. Copy and paste has a limitation on the quantity of information the paste buffer can handle. I don't know what the limit is, but a couple of hundred thousand rows is often more than enough, and in the case of SSMS 2008, it would display that error, and then SSMS would crash, and I'd have to start it up again, and NOT be able to recover the query I was working on... It might be as little as 64KB, and I also don't know if any of the configuration options in SSMS have any control. If that's not what the original poster is talking about, then I have no clue...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 4, 2016 at 4:04 pm
Run perfmon /sys and look at process private bytes for SSMS when it OOMs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply