In our data warehousing system, every weekend a DTS package executes that
copies data out of several SQL Server 2000 'OLTP' databases into one SQL
Server 2000 'warehouse' database. The package uses several Transform Data
tasks to copy the data.

The problem is that every time the package executes the transaction log on
the destination SQL Server gets very big. I have no need for the log and it
causes a problem because it consumes almost all free disk space by the end
of the process. I am not clear if there is a way to minimize the logging. I
have tried setting the recovery model on the destination database to Bulk
Logged but it made no difference. I have looked for options inside the
properties on the Transform Data tasks but have left the defaults as is
because the only one that looks like it could make a difference ('Use fast
load') is already checked.

Can this be done?

I would assume you do not need point in time recovery on the warehouse,
change the recovery to simple mode.

--
Ray Higdon MCSE, MCDBA, CCNA
--
"Laurence Neville" <laurenceneville (AT) hotmail (DOT) com> wrote

In addition. Whilst setting the log to simple will help to keep it down it
won't really help here. If you are moving a large amount of data into SQL
Server using the default settings the data is stored in the Log before being
committed to the DB. Because it is not committed means the log cannot be
cleared of the space it is using. To help things along you can set the
fetch and commit sizes on the last tab of the datapump setup. This coupled
with the SIMPLE recovery mode will help keep the log size down for DTS
packages

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ray Higdon" <rayhigdon (AT) higdonconsulting (DOT) com> wrote

Allan,

I am looking at the Options tab of my Transform Data task, and I see Fetch
Buffer Size and Insert Batch Size fields. Is that what you are referring to?

Laurence


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Yes.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Laurence Neville" <laurenceneville (AT) hotmail (DOT) com> wrote

If you want to learn more about transaction log handling in general, this
is a good reference:
317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL
Server http://support.microsoft.com/?id=317375

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.