[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Insufficient memory

Posted on 2003-10-23
17
Medium Priority
?
1,176 Views
Last Modified: 2008-02-01
I am trying to import a large amount of data into SQL server. Currently the data is in a proprietary format and basically all I have is an executable that automatically exports the data the SQL server.  But it doesn't even get about halfway through when it returns an ODBC error message that says there is insufficient system memory to run this query.  At that point I have to restart the SQL server service because it basically crashes SQL server anything from then on will give me that same error including trying to access other databases.  The SQL server has 1.5Gigs of ram and when the operation is execute it starts with about 1.3 gigs available when it is running I noticed that the SQL server process slowly increases its memory until it gets around 120 megs and then the system dies with its out of memory message but the system clearly has atleast 1.1 gigs still available....
0
Comment
Question by:nwalter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +2
17 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 9608364
Check the 'memory' tab on the SQL Server configuration properties.  It's possible that you're configured to use a max of 120Meg which is where SQL Server will then cap out regardless of how much physical memory is on the server.

Are there other export options from your proprietary format?  You might take a look at exporting the data into a flat file and then using DTS to pull it into your SQL Server.

No guarentees on either of these ideas - just "educated" guesses...

0
 
LVL 1

Author Comment

by:nwalter
ID: 9608388
Yup.. I've spent the last 4 days playing with the memory tab and nothing i do seems to make a difference.  As far as I know there are not a whole lot of export options.  The data is currently in a database called frontbase and the application that is running on it is a forum software called SiteScape.  The executable is an application written by Sitescape that is basically just using SQL queries to copy the data from one database to another.  So as far as exporting it to some other format or doing really anything i've not a clue.  Frontbase's website doesn't offer any information that I could find.. www.frontbase.com ...
0
 
LVL 34

Expert Comment

by:arbert
ID: 9608740
Have you ran profiler to capture the SQL Statements that SiteScape is executing--sounds like it's doing something hokie....
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:nwalter
ID: 9609394
Just ran profiler and nothing out of the ordinary seems to be happening.. The last 4 few lines are :
Set Transaction Isolation Level Read Committed
Delete from...
Delete from...
If @@Trancount > 0 commit Tran

then I get
Audit LogOut
Audit LogOut..

and SQL server dies...
0
 
LVL 1

Author Comment

by:nwalter
ID: 9609417
My best estimate tells me there is atleast 10,000 lines of SQl code before it dies... but thats just a guess ;)
0
 

Expert Comment

by:Mogalappa Adaki
ID: 9609800
What is the service pack that you have applied? If the service pack is less than SP3a, apply latest service and then see.

If it is latest service pack, then make the transaction with minumum number of rows. I will wait for your reply.
0
 
LVL 1

Author Comment

by:nwalter
ID: 9610143
Service pack is 3a but I am not sure I understand how to make the transaction with minimum number of rows?
0
 
LVL 34

Expert Comment

by:arbert
ID: 9610606
"to make the transaction with minimum number of rows? "   Not like you have much of a choice anyway if it's coded in the EXE.

What mogaruna was getting at was instead of deleting a whole table at once, break it down into smaller transactions (1000 rows at once)....
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9611990
Try to import those data in junks. EG: if you import 1 mil records than break it into 250.000 records each...based on the PK... this should help you...If this is not enough break it into more pieces...

good luck,
   xenon
0
 
LVL 34

Expert Comment

by:arbert
ID: 9614391
just wanted to make sure, didn't you say above this is a third party application that does the extract and you can't change it?
0
 
LVL 1

Author Comment

by:nwalter
ID: 9615255
Yeah.. there is no way to import it in pieces.. So I figured out part of the problem lastnight.. I needed to change the collation type to something specific and that worked.. sort of.. It ran for about 10 hours and over 5 million lines of SQL code and then died again with insufficient memory...  SQLserver.exe had actually used up 1.3 gigs of ram.. so thats understandable now but how do I get past this?  DO i need to buy more ram or is there some way I can tell SQL server to page this stuff out to disk because it didn't appear to have paged anything to disk...
0
 
LVL 34

Expert Comment

by:arbert
ID: 9615718
SQL Server should never act like that--whatever application you're running isn't doing something that it should--it must be working with a huge batch size or something.  Are there any errors in the sql log or the event viewer?  Any chance you're running out of tempdb or log space????
0
 
LVL 1

Author Comment

by:nwalter
ID: 9615777
Yeah there seems to be one event in the application log repeated about every 10 seconds...
17424
Warning: Open Objects Parameter may be too low.

I am having trouble opening the SQL server log...
0
 
LVL 1

Author Comment

by:nwalter
ID: 9615809
Ok so the SQL log shows the same text about 5,000 times give or take a few thousand... and at the end it says error 701, Severity 17, State 127

There is insufficient system memory to run this query
0
 

Assisted Solution

by:Mogalappa Adaki
Mogalappa Adaki earned 375 total points
ID: 9615979
Could you tell what are the tables with data types it gets import the data? Or what is the data it gets imported in the SQL database?
Could you send me the database options? What is the recovery model for your database?
Is it your server dedicated SQL Server or any application runs?
Your exe runs on the SQL server or it runs from other machine to export the data to SQL Server.
Could you check the amount of memory consumed by your exe when uploading the data from source to SQL Server?
What is the minimum and maximum memory setting on your SQL Server?
0
 
LVL 34

Accepted Solution

by:
arbert earned 375 total points
ID: 9616049
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_0pm6.asp

I think you need to get with the people that made that EXE. No matter how your SQL Server is configured--this isn't behaving.  It looks like this application isn't releasing resources between operations.....
0
 
LVL 1

Author Comment

by:nwalter
ID: 9672955
Hi,

Looks like I solved this problem...  It was a combination of setting the Collation sequence on the server and increasing the maximum number of objects. I am going to split the points between arbert and mogaruna since they contributed to this the most.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question