Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RBoydell
Starting Member
3 Posts |
 Posted - 2008-08-29 : 12:00:37
|
| I am having considerable difficulty on appending a fairly large text file (11m rows c 2GB within SQL Server 2000) to an existing table within SQL Server 2000. When I try to load the data via DTS from the text file to the existing table it loads the rows very quickly and then stalls at the last point of the load for a few hours before returning an error message telling me I have run out of space in one of data file groups.The curious thing is that I can load the same data from the same text file into a new table in 5 mins without any problems.I do have 55m rows in the existing table (10 GB of SQL Server 2000 disk space) and 5 different indexes on this table. Any ideas on why this might be happening would be most welcome. I am not a DBA and unfortunately the DBA's in the IT department do not know what is happening and cannot help. Roberto |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-08-29 : 12:40:15
|
are you committing the rows in batches, or as a single transaction? it may be the tlog is running out of space.if you were using bcp, I'd say to pass -b100000 or something reasonable. probably there's a similar setting in DTS but I don't know what it would be.
elsasoft.org |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-29 : 13:00:29
|
| Probably due to index because it inserts in index pages as well.Do you have enough space for transaction log to grow? You can put in batches like Jezemine said. |
|
|
|
RBoydell
Starting Member
3 Posts |
Posted - 2008-09-01 : 07:40:26
|
| Thanks guys.It was the indexes. Dropped them and reloaded the data and then recreated te indexes and it has worked. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-01 : 10:15:32
|
| That's the reason why too many indexes are bad for high DML operation table. |
|
|
|
shafi.spl
Starting Member
10 Posts |
Posted - 2008-09-01 : 16:59:47
|
| may be indexes consumed the disk space |
|
|
|
|
|
|
Bulk Insert Failure