problem while trasfering records!!!

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

problem while trasfering records!!!

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-03 : 10:56:10

hello friends

we want to transfer our master table to new server. master table contains near about 6 million records .. we started this by using DTS
-----truncate [new server].[owner].tablename
-----insert into [new server].[owner].tablename
-----from select * from master table
but after 1 and half hour we stopped it because we found log file was near about 18 GB!!!!

we want to do this daily once can any body guid me what i will do for it...log file also keep in factor and how much time it will take to finish DTS...we have 2 GB RAM....

T.I.A

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 11:11:05

You could do it in SQL (rather than DTS):

insert into [new server].[owner].tablename AS NEW
select TOP 1000 * from [old server].[owner].tablename AS OLD
WHERE NEW.MyPK NOT IN (SELECT OLD2.MyPK from [old server].[owner].tablename AS OLD2)

and loop round, preferably doing a TLog backup after a suitable number of loop iterations

The bit about SELECT TOP 1000 and NOT IN (... list of PKs ...) is going to need work, because as I've written it its going to be too slow.

This thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72804

is about deleting, but similar issues about controlling log size, and not crippling performance of other activities on the server, are in common with your situation, and thus might help.

EDIT: BCP out and back in again might be a better bet

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-03 : 11:11:23

Why don't you use COPY SQL SERVER OBJECTS task...I believe that will be faster.

Harsh Athalye
India.
"Nothing is Impossible"

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-03 : 11:24:28

hi harsh ..
can you guide me pls..

thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-03 : 11:47:54

quote:
Originally posted by under2811

hi harsh ..
can you guide me pls..

thanks

I guess you are using EXECUTE SQL task in DTS to carry out your data transfer operation...instead of that why don't you use COPY SQL SERVER OBJECTS task and specify Source and destination server and the table which you want to copy.

Harsh Athalye
India.
"Nothing is Impossible"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-03 : 12:08:36

quote:
Originally posted by harsh_athalye

Why don't you use COPY SQL SERVER OBJECTS task...I believe that will be faster.

Harsh Athalye
India.
"Nothing is Impossible"

What underlying architecture does Copy SQL Server Objects use? Doesn't it use a transfer data task just like typical dts? If that is the case then question it being faster ...

Jay White

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-10-03 : 13:47:31

On the last page of the data transformation task, there should be a small text box for how many records to commit in batches. The default is all of them. Try 100,000 or so.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-04 : 00:18:00

hi harsh.

i not just copy the table but daily i have to truncate the data from destination server and then fill the destination table with source table from source server (records about 6 to 8 million)...so i have to consider that also and log generation also... :(

help me out if any one knows this track

T.I.A

Kristen
Test

22859 Posts

Posted - 2006-10-04 : 01:44:35

6 to 8 million records is a lot of logging!

I think you would be better with BCP out [from source server] and back in [destination server] again

EDIT: Actually I suppose if the Destination servers RECOVERY MODEL is SIMPLE it wouldn't be so bad

EDIT2: Damn! I already said that earlier - getting old [:-(]

Kristen

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-04 : 05:06:47

hello ppls..

i am still running under vague situation..same thing if i did on my local servers then the records got tranformed in expected time but if i did it on client side servers then i am stuck....

i am using locally as well as on client side normal DTS packages...i.e truncating on dest. table and then fill it

pls help me out

T.I.A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources