I have a DTS job which copies 20 tables from a Sybase database to Microsoft
SQL Server database.

The sybase connectivity is a ODBC Connection, and SQL server native
connection.

The tasks are deleting from the SQL Server tables, and then Copy from Sybase
to SQL Server tables (select * from .. destination object).

Whenever I run this, the log file increases by 4000 MB for every run.

Is there a way I can avoid this logging.

One option but it depends on your database, If you are
deleting all records in the SQL Server table, try truncating
the tables instead of deleting. If you have constraints that
prevent this, drop the constraints, truncate the tables,
recreate the constraints. Truncate logs the page
deallocations instead of each row deletion.

-Sue

On Tue, 8 Feb 2005 07:25:08 -0800, Yog
<Yog (AT) discussions (DOT) microsoft.com> wrote:

Truncating is definitely a good suggestion as it beats DELETE performance.
You can also set the recovery model to Simple (DB Optionstrunc log on chkpt
= true, select into/bulk copy = 'true') or Bulk Logged (select into/bulk
copy = true ) to minimize logging during the load. Bear in mind, *you will
only be able to recover to the last full backup should you need to restore*,
so you'll want to review the backup and recovery reuqirements of your
environment.


"Sue Hoegemeier" <Sue_H (AT) nomail (DOT) please> wrote

Also note that even though these options are set you may still blow the
log. SQL Server cannot get rid of any thing from the log until it has
written it to disk. For SQL Server to be able to do this it will have
to breathe during the load and this can be accomplished by "Batching"
the rows into chunks.



"Andy S." <andymcdba1 (AT) nospam (DOT) yahoo.com> wrote

Thank you, I will try these options.

"Allan Mitchell" wrote: