| Author |
Topic |
|
Haarish
Starting Member
30 Posts |
 Posted - 2009-05-28 : 09:08:12
|
| Hi All,I executed an SSIS package that imported 12 million rows into a table on the SQL Server. The package execution was successful and the results showed that it had moved all the 12 millon rows to the DB. But when I did a select * from <table> it returned 21581 rows and then errored out saying 'System: Out of memory exception'. Doubting that this could be a problem with SSMS, I executed the same query with SQLCMD utility and it gave me the same results. Can any one tell me what could be wrong?Thanks in advance,Haarish. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-28 : 09:14:50
|
How "big" is the table? How many colunms?Execute "SELECT COUNT(*) FROM Table1" to see the record count.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Haarish
Starting Member
30 Posts |
Posted - 2009-05-28 : 09:17:34
|
| The table has 5 columns. When I did a select count(*) from <table> it gave me 21581, 5 more than what I got in the results for the select * from <table> query!. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-28 : 09:20:39
|
Do you have an index on the table with option "discard duplicate records" set to on?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Haarish
Starting Member
30 Posts |
Posted - 2009-05-28 : 09:24:29
|
| I havent built an index on the table as yet. The table was created in the SSIS package itself based on the Output columns of the flat file source. Intending to create an index after the data was imported, I queried the table to check if all the data is in. On a side note, my DB server has about 60 databases in it though they are not very large as this DB and their .mdf and .ldf files reside elsewhere, would there still be a problem? |
|
|
|
Haarish
Starting Member
30 Posts |
Posted - 2009-05-28 : 09:28:17
|
| Also, I had executed a query to truncate the table, the query has been executing for more than 30 minutes now. Techically truncating a table with 21500 rows shouldn't take that long right? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-28 : 09:32:08
|
Depends on indexes and stuff and other thingies.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Haarish
Starting Member
30 Posts |
Posted - 2009-05-28 : 09:34:57
|
| There is ample space on each of my drives and I do not think that is an issue here. Is there a way to isolate the issue i.e. if it is a SMSS issue or a problem with the SQL Server itself? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-05-28 : 13:03:58
|
quote:
Originally posted by Haarish
Hi All,I executed an SSIS package that imported 12 million rows into a table on the SQL Server. The package execution was successful and the results showed that it had moved all the 12 millon rows to the DB. But when I did a select * from <table> it returned 21581 rows and then errored out saying 'System: Out of memory exception'. Doubting that this could be a problem with SSMS, I executed the same query with SQLCMD utility and it gave me the same results. Can any one tell me what could be wrong?Thanks in advance,Haarish.
Well I have seen that error before because SSMS (Mangement Studio) could not allocate enough memory to hold all the results returned for display. If you are trying to truncate a table and it is taking a long time. You should look to see if something is blocking your query. Perhaps an open transaction is haning around...? |
|
|
|
Haarish
Starting Member
30 Posts |
Posted - 2009-05-29 : 00:32:38
|
| Lamprey, But if that was a memory issue with SSMS, wouldn't the SQLCMD utility return the correct results? |
|
|
|
Haarish
Starting Member
30 Posts |
Posted - 2009-05-29 : 02:28:06
|
| I am also using SQL Server 2008 Standard Edition, does that throw aby pointers to you guys? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-01 : 11:46:44
|
quote:
Originally posted by Haarish
Lamprey, But if that was a memory issue with SSMS, wouldn't the SQLCMD utility return the correct results?
I would assume so. Have you checked the SQL settings for memeory and what it is using? I've seen some strange memory issues in the past when there was some application that was running to do some data loading and it did not deallocate memory properly when it was done, thus causing the system to starve for available memory.. just a thought. |
|
|
|
|
Out of Memory Exception