Getting an error "Exception of type 'System.OutOfMemoryException' was thrown." during stored procedure execution

Question

Getting an error "Exception of type 'System.OutOfMemoryException' was thrown." during stored procedure execution

dsachu

SSCrazy

Points: 2242
More actions
May 9, 2011 at 12:00 am

#239773

Hi
I am running a stored procedure that have lacs of select command but query is showing this error.
"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."
Regards,
Sachin.

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

Can you provide some more information. Google has lots of hits for this but it would be helpful to know the full error, what sql version you using, whether it is for a web based app and what the sp is doing.

Adam Zacks-------------------------------------------Be Nice, Or Leave

dsachu SSCrazy Points: 2242 More actions · Answer 2

Thanx for your reply..

Actually i am using sql server 2008....and using such thing for sp

Regards,

Sachin.

dsachu SSCrazy Points: 2242 More actions · Answer 3

sp is using for generating report.....i am simply run my sp in query analyser

during the sp execution such type error comming.

My sp is more complicated and it's taking lot of time...on that period such error is occurring.

Regards,

Sachin.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 4

kumar.sachu08 (5/10/2011)
Thanx for your reply..
Actually i am using sql server 2008....and using such thing for sp
Regards,
Sachin.

Yes but please provide the FULL error messagae (all the output shown when s p fails).

And give us an idea of what the sp is used for.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 5

kumar.sachu08 (5/10/2011)
sp is using for generating report.....i am simply run my sp in query analyser
during the sp execution such type error comming.
My sp is more complicated and it's taking lot of time...on that period such error is occurring.
Regards,
Sachin.

Please paste the whole error.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Jonathon Williams SSC Veteran Points: 261 More actions · Answer 6

That is the entire error message. I'm getting it as well:

Query:

Select Non Empty [Measures].[Admissions Actual] on columns,

[Patient].[Patient ID].Members on Rows

From [Gentiva DW]

Where ([Business Unit].[Cost Center].&[0075],[Date].[Calendar].[Year].&[2012])

Entire Messages:

Executing the query ...

Exception of type 'System.OutOfMemoryException' was thrown.

Execution complete

Jonathon Williams SSC Veteran Points: 261 More actions · Answer 7

Server has 16 GB of RAM of which 6.64 is used...

It's a 64-bit Windows Server 2008 R2 Enterprise with Intel Xeon CPU X6550 @ 2.00 GHZ (4 Processors)

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 8

how many rows are expected?

how you performed an execution plan?

where are you running this?

can you supply the actaul query?

Adam Zacks-------------------------------------------Be Nice, Or Leave

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 9

Jonathon Williams (4/18/2012)
Server has 16 GB of RAM of which 6.64 is used...
It's a 64-bit Windows Server 2008 R2 Enterprise with Intel Xeon CPU X6550 @ 2.00 GHZ (4 Processors)

Are you running the query on the server (ie via rdp) or on a desktop?

Adam Zacks-------------------------------------------Be Nice, Or Leave

Jonathon Williams SSC Veteran Points: 261 More actions · Answer 10

Figured it out... It was my desktop running the SSMS and executing the query that ran out of memory

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 11

well yes that would normally be the cause of 'out of memory', but there are things you can do to prevent that.

Adam Zacks-------------------------------------------Be Nice, Or Leave

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 12

Schadenfreude-Mei (4/18/2012)
well yes that would normally be the cause of 'out of memory', but there are things you can do to prevent that.

I have also seen this ty of behavior when I run fr my local machine....how do you prevent that?

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 13

sqldba_newbie (4/23/2012)
Schadenfreude-Mei (4/18/2012)
well yes that would normally be the cause of 'out of memory', but there are things you can do to prevent that.
I have also seen this ty of behavior when I run fr my local machine....how do you prevent that?

If it is a large query you may want to break it down or copy to temp tables or something. Ultimately though you might have to run it from the server. Its sounds a bit stereotypical but a reboot of your local machine may also help.

Adam Zacks-------------------------------------------Be Nice, Or Leave

pradeep.mohan SSCrazy Points: 2849 More actions · Answer 14

Hi,

I am also facing the same issue in my production database whenever right click database >> Task >> restore >>database.

Server configuration :

32 GB RAM

64 bit O/S

2 processors

Intel(R) Xeon(R) E5620 @2.40GHZ

service pack 2

SQL server 2008R2

I think this is sufficient information about the server.

In that server over all 750 databases which around 60 GB. And log shipping configure around 700 databases.

Error screenshot attached for ref.

Thanks.

Pradeep