SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error-Not enough storage is available to complete this operation.


Error-Not enough storage is available to complete this operation.

Author
Message
GoGreen
GoGreen
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 30
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69609 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37282 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69609 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

GTR
GTR
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1999 Visits: 368
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!
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37282 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69609 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

GoGreen
GoGreen
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 30
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69609 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

GoGreen
GoGreen
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 30
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search