• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

SQL Server 2008 Out of Memory

Hi..
I'm running a large stored procedure on SQL 2008 64bit and I get the following error
. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
It uses a cursor and the server has 16GB of memory.
Any ideas?
thanks
0
JElster
Asked:
JElster
  • 6
  • 4
1 Solution
 
David L. HansenProgrammer AnalystCommented:
That seems like an awful lot of memory to get eaten up.  I suspect the stored procedure is not running correctly.
0
 
JElsterAuthor Commented:
The database has 100M rows. .The SP updates a table with 10M.
0
 
David L. HansenProgrammer AnalystCommented:
The stored procedure is updating 10 million rows??
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
JElsterAuthor Commented:
yes
0
 
David L. HansenProgrammer AnalystCommented:
I'd break up the procedure into multiple ones.  So if your procedure handled all customers, for example, I'd have one procedure to handle last_names from A to F then one from G to L and so on.  Is this doable in your situation?  This is just off the top of my head.
0
 
David L. HansenProgrammer AnalystCommented:
Hope that helps.  By the way, you could still accomplish all of this through one procedure (just have it use the other in whatever sequence makes the most sense.
0
 
JElsterAuthor Commented:
Must be some SQL settings to avoid this..
0
 
David L. HansenProgrammer AnalystCommented:
Perhaps, I'll look into it.  
BTW, are you using transactions in the script?  Transactions with rollbacks being held as a possible option, greatly impacts the amount of memory needed to accomplish such tasks.
0
 
JElsterAuthor Commented:
No tranascations
0
 
David L. HansenProgrammer AnalystCommented:
Here are the best tips I can give you as far as settings are concerned:
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now