SQL Server Performance Forum – Threads Archive

DTS Newbie – TempDB file

Hi, When DTS is transferring data, does it use tempdb ?
If so, can we avoid it so that tempDB won’t fill up when loading
Millions of rows ? Thanks, Paul

---

Yes dpending on the work load to save data it uses tempdb and can not be avoided.

---

Ranjitjain, Thanks, I thought so. I have 25Million rows to copy from Oracle to Sql Server every month. Do I need to somehow configure tempDB to handle the load ? Is their a "better way" to do this.
I would rather do a Bulk Load BCP process. But then I would have to somehow Export the Oracle Table(s) to files
and FTP over and then BCP load. Regards, Paul

---

Ranjit can you provide a source for information that DTS always uses tempdb?

---

Hi again, FWIW, I ran the DTS package "on the destination sql server" with
Options:
Fetch Buffer = 1000 to get 1000 Oracle rows at once
Batch = 5000 rows to commit 5000 sql rows at once. This should minimize the tempDB space issue.
In another thread, I mentioned that the total time to transfer 20Million rows
was about 10 hours (which is acceptable right now, it will be done infrequently). I used TaskPad to view the TempDB used / unused space and it was just fine. Thanks, Paul

---

]]>