| Author |
Topic |
|
nvisibhm
Starting Member
23 Posts |
 Posted - 2004-10-08 : 14:20:25
|
| I am trying to use DTS in SQL Server 2000 to transfer 6.5 million records (8 to 9 Gig of data). In doing this, every time it gets to the end it say the transaction log is full and cannot complete the transfer. I really don't care about the log. I just need to transfer the data. What steps do I need to take in setting up the database so that the log file is not affected by the transfer? So far I have uncheck auto grow log and set recovery model to simple. I appreciate the help.NV |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 14:24:48
|
| Double click on your transformation, go to the Options tab. Change the Insert batch size to maybe 100000 or so. Each batch will get committed after the size has been reached thus helping you with the tlog.Tara |
 |
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-08 : 14:32:45
|
| where do i double click on the transformation? i am going to all tasks import data to do the import.NV |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 14:33:39
|
| Inside the DTS package. Or are you doing this through the wizard?Tara |
|
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-08 : 14:34:14
|
| the import wizard.NV |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 14:35:47
|
| You aren't going to be able to do this through the wizard. At the end of the wizard, there is an option to save the package. Do this instead of running it immediately. Then go to Data Transformation Services, Local Packages, and open up your package. There should be a dark arrow in between two objects. That's your transformation.Tara |
|
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-08 : 14:40:11
|
| ok, i am hunting for the options tabNV |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 14:40:47
|
| Did you double click on the transformation arrow?Tara |
|
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-08 : 14:44:05
|
| ok, i follow you. do i check always commit final batch?by the way, thank you very much for your help.NV |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 14:48:09
|
| I don't think I woud select that option. It just depends if you want some of the rows committed if an error occurs.Tara |
|
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-08 : 14:53:44
|
| i right clicked on the dark arrow and selected execute step. does this run the dts package? I will let you know if your solution works. If it does, which I think it will, then you are definitely the SQL Warrior Princess.NV |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 14:55:29
|
| You can do it that way for this package since that's the only step there is. In other more complex packages, you might have additional steps, so you just need to hit the green arrow at the top of the screen to execute the entire thing. There's also an execute option off the menu.Tara |
|
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-08 : 15:02:58
|
| ok, it is still failing, but now it fails at 100000 rows. do i need to let the log file auto grow nowNV |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 15:06:09
|
| It just depends on how much free disk space that you have. I always let the tlog file auto grow as we've got tons of free disk space available. Anyway, try that and also try reducing the batch size from 100000. It'll be slower to insert them, but at least it'll work. Try maybe 10000 at a time. 6.5 million rows is going to take a bit anyway.Tara |
|
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-08 : 15:12:02
|
| i set it to 50000 and i changed to autogrow. it made it past 50000, so i may be good. it will probably be a couple of hours before i know. thanks again for the help.NV |
|
|
|
nvisibhm
Starting Member
23 Posts |
Posted - 2004-10-11 : 16:45:28
|
| it worked. thank you very much for the help.NV |
|
|
|
|
DTS transfer large amount of data