Link to home
Start Free TrialLog in
Avatar of nwalter
nwalter

asked on

Insufficient memory

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....
Avatar of frodoman
frodoman
Flag of United States of America image

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

Avatar of nwalter
nwalter

ASKER

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 ...
Have you ran profiler to capture the SQL Statements that SiteScape is executing--sounds like it's doing something hokie....
Avatar of nwalter

ASKER

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...
Avatar of nwalter

ASKER

My best estimate tells me there is atleast 10,000 lines of SQl code before it dies... but thats just a guess ;)
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.
Avatar of nwalter

ASKER

Service pack is 3a but I am not sure I understand how to make the transaction with minimum number of rows?
"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)....
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
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?
Avatar of nwalter

ASKER

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...
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????
Avatar of nwalter

ASKER

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...
Avatar of nwalter

ASKER

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
SOLUTION
Avatar of Mogalappa Adaki
Mogalappa Adaki

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nwalter

ASKER

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.