Solved

Insufficient memory

Posted on 2003-10-23
17
1,164 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 125 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 125 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

786 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