Solved

Insufficient memory

Posted on 2003-10-23
17
1,162 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

12 Experts available now in Live!

Get 1:1 Help Now