SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Error While Retrieving Record from Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pradeep_iete
Yak Posting Veteran

India
84 Posts

Posted - 01/23/2010 :  05:09:08  Show Profile  Reply with Quote
Hi All,
I have a table containing total ROws : 68603904.
When i write a Query , SELECT * FROM <Table> It gets executed for 22 minutes and then query cancelled with error :
"An error occurred while executing batch. Error message is: There is not enough space on the disk."

My System Configuration is
Processors : Intel (R) Xeon (R) CPU 2 Processor of 2 GHz each
Memory (RAM) : 4.00 GB
System-Type = 32 bit OS
SQL Server = 2005 Standard

Thanks

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/23/2010 :  05:26:35  Show Profile  Reply with Quote
You are trying to pull all columns (maybe some of them are hugh blobs of text / images?) from the table which contains 68 million rows.

And you are running out of disk space ....

What are you trying to do, and what ([i.e. application] are you making the query from?
Go to Top of Page

pradeep_iete
Yak Posting Veteran

India
84 Posts

Posted - 01/23/2010 :  05:49:45  Show Profile  Reply with Quote
Well,
Total Columns are 50
Out 50 ,
44 Columns are DECIMAL(12,2)
4 COumumn are VARCHAR(20)
1 Column are Datetime
I am running query from Console itself and i want my complete results in grid but i m not able to do that?
What other option i can try to achieve that.
Please feel free to ask more information
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/23/2010 :  07:18:56  Show Profile  Reply with Quote
I very much doubt that you can pull 68 millions rows to the console!! What would you then do with them? Copy to clipboard? Check them manually? I just can't imagine being able to anything with that amount of data on screen ...

If you want them in a file then you can do that with BCP or SSIS, indeed Save to File in console (set BEFORE you run the query, not after) would probably work ...

... the file is going to be about 20GB - give or take a couple of powers of 2.
Go to Top of Page

pradeep_iete
Yak Posting Veteran

India
84 Posts

Posted - 01/25/2010 :  01:40:59  Show Profile  Reply with Quote
Hi Freind ,
I have a doubt,If the Table is 20 Gb in Size and i am firing query : SELECT * FROM <Table> , Then does it means that the complete 20 gb data will come in Data CACHE of SQL Server assuming that Data Cache has that accomodation.
From there we might reduce I/O cycle for further reference requried by same table.
I need gets my fundamental right cause i have an age of 1 and a half years in Database Administration.
Please illuminate my concepts ?

Thans
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/25/2010 :  02:17:42  Show Profile  Reply with Quote
The 20GB will be store in MEMORY on YOUR computer. I don't suppose you have 20GB memory installed, so it will be using Paging File.

SQL Server has no difficulty delivering 69,000,000 rows when you do SELECT * FROM MyTable - but the transmission of that data to the Console, and the Console trying to display it, requires that the Console store it in memory, tries to display it in a grid, and so on.

What do you want to do once the 68,000,000 is displayed on your console screen?
Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 01/25/2010 :  13:04:54  Show Profile  Click to see tripodal's MSN Messenger address  Reply with Quote
Does SQL put this querey in a temp table before it transmits it to the console?

"An error occurred while executing batch. Error message is: There is not enough space on the disk."

Looks like a SQL Error, happening before your client dies from lag / out of memory

Edited by - tripodal on 01/25/2010 13:08:57
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/25/2010 :  15:29:15  Show Profile  Reply with Quote
This is funny

Are you doing this in SSMS?

Or are you trying to creat a file?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/26/2010 :  02:32:36  Show Profile  Reply with Quote
"Does SQL put this querey in a temp table before it transmits it to the console?"

Nope.

EDIT: Actually, not entirely true, if there is an ORDER BY, or some complexity, it may use TEMPDB as working storage. You'd get a different message if that ran out of disk space though.

Edited by - Kristen on 01/26/2010 02:33:34
Go to Top of Page

pradeep_iete
Yak Posting Veteran

India
84 Posts

Posted - 01/27/2010 :  01:13:34  Show Profile  Reply with Quote
Hi Friends
Now my next excersize is for same table is as follows :
Actually i was trying to see the number of records that has been fetched in sampled time of 30 seconds for different values of following configurations.

sp_configure 'network packet size (B)',4096
sp_configure 'network packet size (B)',16384
sp_configure 'network packet size (B)',32767

But i see invariably same number of records fetched.In other words not much impact has been observered considering various configurations for "network packet size".Please elaborate on that and correct me where ever needed?
Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 01/28/2010 :  11:37:56  Show Profile  Click to see tripodal's MSN Messenger address  Reply with Quote
The tcp/ip settings of your network cards is likely set with an MTU of 1500. You would require a switch and network card that supports jumbo frames. Adjusting the settings w/o reconfiguring the network may only give you a benefit on the local machine.

Your network utilization which can be tracked in perfmon, will give you an indication as to how well you are using yoru bandwidth.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000