Solved

Insufficient memory

Posted on 2003-10-23
17
1,163 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
 
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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now