Unless you are doing a minimally logged load (and I doubt you are) you will
always log at least as much data as you change or insert. Actually more
since there is some overhead as well. When you say increased do you mean
the physical size of the file or the amount of log space used?

--
Andrew J. Kelly SQL MVP


"Roy Goldhammer" <roy (AT) hotmail (DOT) com> wrote

Look in BooksOnLine under "minimally logged bulk copy" for the conditions to
get a minimally logged load. While this is for BCP the rules still apply to
DTS loads. The key mostly with DTS is to make sure the TABLOCK checkbox is
enabled along with the other conditions mentioned. When you meet these
requirements you will only log the extent numbers of extents that were
modified during the load and not the actual data. This can reduce the
amount of data sent to the tran log dramatically and also speed up your
operation.

--
Andrew J. Kelly SQL MVP


"Roy Goldhammer" <roy (AT) hotmail (DOT) com> wrote

Hello there

I've run dts between two servers that connected by the internet, and one of
the tables failed.

I've transfered 78Mb, and the log file has increased in 100Mb.

Whayd did the log file has increased that mutch?
and how can i prevent it?

Yes Andrew

The destination server is website which is limited to 256Mb of data and
256mb for Log

you said "minimally logged". what did you mean by that?

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Usually all you need to do when using a DTS package is to make sure the
checkbox on the advanced options tab of the package for "Use TableLock" is
turned on.

--
Andrew J. Kelly SQL MVP


"Roy Goldhammer" <roy (AT) hotmail (DOT) com> wrote

Thankes Andrew

It took me time to find out about it

It seems that i only need to use sp_tableoption 'tablename', 'table lock on
bulk load', 'true'

But now have another problem.

I couldn't run this store procedure on tables who is not on my connection.
and if i reset the data(running script that rebuild it againg this option
return to default)

Is there a way to maintain this option?

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote