March 12, 2010 at 5:03 pm
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.
March 12, 2010 at 5:09 pm
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[/url]
Learn Extended Events
March 13, 2010 at 4:15 am
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
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 10:59 am
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[/url]
Learn Extended Events
March 13, 2010 at 7:22 pm
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?
March 14, 2010 at 3:14 am
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
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 3:37 pm
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[/url]
Learn Extended Events
March 14, 2010 at 5:38 pm
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.
March 14, 2010 at 5:49 pm
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[/url]
Learn Extended Events
March 14, 2010 at 8:48 pm
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.
March 14, 2010 at 10:12 pm
GoGreen (3/14/2010)
OS is not the issue here
I tend to disagree.
GoGreen (3/14/2010)
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
Sample of the real VB code please.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 10:27 pm
Paul White (3/14/2010)
GoGreen (3/14/2010)
OS is not the issue hereI tend to disagree.
GoGreen (3/14/2010)
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
Sample of the real VB code please.
Paul
I agree with Paul. Please provide a sample of your code.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 16, 2010 at 12:18 pm
I've seen that error on SQL 2000 when the server was out of memory (don't recall if it was SQL Server or a linked server). This was an older server with 4 GB RAM and lots of processing going on. Could your server be low on memory?
March 17, 2010 at 4:59 am
Linda Johanning (3/16/2010)
I've seen that error on SQL 2000 when the server was out of memory (don't recall if it was SQL Server or a linked server). This was an older server with 4 GB RAM and lots of processing going on. Could your server be low on memory?
It is normally something of that nature, though more usually a shortage of some other OS-level resource.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 10:27 am
Is the OP still out there?
Is this still an issue?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply