SQL Server - Limiting Log Growth during DTS Package

Asked By davisutt

26-Jul-07 02:51 PM

SQL Server 2000 SP4.  I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing, updates, and date
verification
and loads to a SQL Server DB.  Most of the tables are full refresh
and
a few are incremental.

Main DW:  DwSQL
Staging Area: DwLoadAreaSQL


The DW is about 60 Gigs.  The Staging Area is about 80 Gigs.  This is
all good.


However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file.  I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all
1
transaction.  I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping
to
avoid this.  To be clear, I know how to shrink DB's and Log
Files...that's not the issue.


Any Ideas?  Thanks.

28-Jul-07 12:42 AM

Change the database model to SIMPLE so that you don't bother with log
files.  You can always re-grab the ORACLE data and re-run your process
if you experience any sort of problem for which keeping the
TRANSACTION LOG might have been helpful.

(I have a process that uses SQL Server for lots of computations.  The
process routinely inserts and updates millions of rows in a matter of
minutes.  While debugging the process I may run it dozens of times in
a day.  But I never have a need to log the various INSERT and UPDATE
transactions, since if there is ever a problem, I can merely re-run
the process to generate all the necessary output tables from the
input.)

Will that work for you?