| Author |
Topic |
|
pradeep_iete
Yak Posting Veteran
84 Posts |
 Posted - 2010-01-23 : 05:09:08
|
| 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 StandardThanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-23 : 05:26:35
|
| 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? |
 |
|
|
pradeep_iete
Yak Posting Veteran
84 Posts |
Posted - 2010-01-23 : 05:49:45
|
| Well, Total Columns are 50 Out 50 , 44 Columns are DECIMAL(12,2) 4 COumumn are VARCHAR(20) 1 Column are DatetimeI 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-23 : 07:18:56
|
| 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. |
|
|
|
pradeep_iete
Yak Posting Veteran
84 Posts |
Posted - 2010-01-25 : 01:40:59
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-25 : 02:17:42
|
| 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? |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2010-01-25 : 13:04:54
|
| 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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 02:32:36
|
| "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. |
|
|
|
pradeep_iete
Yak Posting Veteran
84 Posts |
Posted - 2010-01-27 : 01:13:34
|
| Hi FriendsNow 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)',16384sp_configure 'network packet size (B)',32767But 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? |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2010-01-28 : 11:37:56
|
| 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. |
|
|
|
|
Error While Retrieving Record from Table