Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Error-Not enough storage is available to complete this operation. Expand / Collapse
Author
Message
Posted Friday, March 12, 2010 5:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 12:17 AM
Points: 6, Visits: 27
Hi All,

I am using SQL Server 2005 .
I am trying to import data from file(txt, xls) using multiple Insert statement.
Is there any limitation on query size that SQL server can handle at one time?

Really appreciate any response.
Post #882209
Posted Friday, March 12, 2010 5:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
That error relates to an out of space situation in your log file or data file. Make sure your files are properly sized.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #882210
Posted Saturday, March 13, 2010 4:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
GoGreen (3/12/2010)
I am trying to import data from file(txt, xls) using multiple Insert statement.
Is there any limitation on query size that SQL server can handle at one time?

That looks more like an OS error to me..."not enough storage is available to process this command."

I am intrigued by your statement above.
Are you are trying to execute an enormous batch of single-row INSERT statements?
Please give more details and a small example...there are much, much, better and faster ways of doing this, if so.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882333
Posted Saturday, March 13, 2010 10:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
Paul White (3/13/2010)
GoGreen (3/12/2010)
I am trying to import data from file(txt, xls) using multiple Insert statement.
Is there any limitation on query size that SQL server can handle at one time?

That looks more like an OS error to me..."not enough storage is available to process this command."

I am intrigued by your statement above.
Are you are trying to execute an enormous batch of single-row INSERT statements?
Please give more details and a small example...there are much, much, better and faster ways of doing this, if so.

Paul


I just forced the same sort of error. I processed a couple of GIGs and disabled the autogrowth on the log file. I also kept the log file small.

Thus I would say yes it is a big file being processed and there are other factors at play such as autogrow, disk space, small log or data file, or something of that nature.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #882394
Posted Saturday, March 13, 2010 7:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 20, 2013 9:25 AM
Points: 381, Visits: 357
It can be any either OS issue like size or mdf and ldf file size cannot grow depending on the file growth settings, as others said need more info. How much free space you have on disk? Is database mdf and Ldf file is set to autogrowth? If so what is the percentage or size it is set at?

EnjoY!
Post #882449
Posted Sunday, March 14, 2010 3:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
CirquedeSQLeil (3/13/2010)
I just forced the same sort of error. I processed a couple of GIGs and disabled the autogrowth on the log file. I also kept the log file small.

Jason,

Can you share some more details of what you did please?

I imagine you created a very large single batch of single-row INSERT statements, but did you run it from SSMS or a command-line tool?
I would expect SQL Server to return a more specific message if the log or database files fill - but am open to the possibility that an external tool might not pass the details on to the client application. The "not enough server storage..." message is very generic - a bit like error 5

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882539
Posted Sunday, March 14, 2010 3:37 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
I would have to go back and recreate it again. SQL 2000 box SP3 and SQL 2005 RTM box. I was copying a table with a column of type text to a new temp table on the 2k5 box, and then changing the field to varchar(max). During the copy I got the generic message along with the log file full message. This was from within SSMS. ON the 2K box, I was copying the temptable via dts back to the 2000 box and generated the same sort of errors.


If I recall better though, I think the generic message was a part of the email alert that had been fired off. The email alert system was built off DMO. Maybe that could play a part in it.






Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #882627
Posted Sunday, March 14, 2010 5:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 12:17 AM
Points: 6, Visits: 27
hi all,
Thanks for your feedback.

OS is not the issue here nor the space still I have more than enough remaining on my disk.

I am using multiple INSERT Statement to build large SQL statement...
For eg
I read from file differnet column values and build Simple INSERT statement...
file may contain more than 20,00 Rows /lines
for i=0 to No.Of Rows
INSERT INTO (...)
next
If my file large enough it bombs me out!!!!
What should be optimized solution, that it would not effect if run on different system with minimum configuration?
Should I process SQL in small batches ??? Then what should be size limitation on that?

Really appreciate your answer.
Post #882642
Posted Sunday, March 14, 2010 5:49 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
GoGreen (3/14/2010)
hi all,
Thanks for your feedback.

OS is not the issue here nor the space still I have more than enough remaining on my disk.

I am using multiple INSERT Statement to build large SQL statement...
For eg
I read from file differnet column values and build Simple INSERT statement...
file may contain more than 20,00 Rows /lines
for i=0 to No.Of Rows
INSERT INTO (...)
next
If my file large enough it bombs me out!!!!


From your description, it sounds like a custom error message that you are generating somewhere.
You are using an iterative process, and should use a set-based method to do this (more efficient)
It also sounds like you are using a home grown app (not sql and not ssis) which could be chewing up a lot of resources.

What application, coding language are you using?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #882645
Posted Sunday, March 14, 2010 8:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 12:17 AM
Points: 6, Visits: 27
The error message is not custom build .
May be the way SQL is build in my application could use resources ,not sure about it.
As its simple going through differnet object and gets values to build up INSERT statement.

I am using VB 6.0 as coding language, SQL Server Management Studio 2005
Can any one please elobrate on Set -based method?

Thank you.
Post #882664
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse