<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Development
»
Error-Not enough storage is available to...
20 posts, Page 1 of 2
1
2
»»
Error-Not enough storage is available to complete this operation.
Rate Topic
Display Mode
Topic Options
Author
Message
GoGreen
GoGreen
Posted Friday, March 12, 2010 5:03 PM
Forum Newbie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 7:40 PM
Points: 6,
Visits: 25
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
SQLRNNR
SQLRNNR
Posted Friday, March 12, 2010 5:09 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
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 2008
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
Paul White
Paul White
Posted Saturday, March 13, 2010 4:15 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 7:18 PM
Points: 10,989,
Visits: 10,532
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
SQLRNNR
SQLRNNR
Posted Saturday, March 13, 2010 10:59 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
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 2008
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
GTR
GTR
Posted Saturday, March 13, 2010 7:22 PM
Old Hand
Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:15 PM
Points: 389,
Visits: 351
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
Paul White
Paul White
Posted Sunday, March 14, 2010 3:14 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 7:18 PM
Points: 10,989,
Visits: 10,532
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
SQLRNNR
SQLRNNR
Posted Sunday, March 14, 2010 3:37 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
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 2008
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
GoGreen
GoGreen
Posted Sunday, March 14, 2010 5:38 PM
Forum Newbie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 7:40 PM
Points: 6,
Visits: 25
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
SQLRNNR
SQLRNNR
Posted Sunday, March 14, 2010 5:49 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
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 2008
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
GoGreen
GoGreen
Posted Sunday, March 14, 2010 8:48 PM
Forum Newbie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 7:40 PM
Points: 6,
Visits: 25
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 »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
