Limiting log growth during DTS Package

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing 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.

Re: Limiting log growth during DTS Package

On Jul 24, 3:49 pm, davisutt wrote:
> SQL Server 2000 SP4. I built a large DTS package that grabs a number
> of tables from an Oracle DB, does some scrubbing 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.

Make sure both databases are in bulked log recovery mode. You should
have a step at the end of your DTS to run CHECKPOINT, backup truncate
the log. Also, manaually shrink the log file to your desired log size.

Hope it helps...

MNDBA

Re: Limiting log growth during DTS Package

On Jul 28, 4:55 pm, kmounkh...@gmail.com wrote:
> On Jul 24, 3:49 pm, davisutt wrote:
>
>
>
>
>
> > SQL Server 2000 SP4. I built a large DTS package that grabs a number
> > of tables from an Oracle DB, does some scrubbing 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.
>
> Make sure both databases are in bulked log recovery mode. You should
> have a step at the end of your DTS to run CHECKPOINT, backup truncate
> the log. Also, manaually shrink the log file to your desired log size.
>
> Hope it helps...
>
> MNDBA- Hide quoted text -
>
> - Show quoted text -

Thanks. I think that's what I was looking for. I changed the
recovery mode and shrunk the log size to about 20% of what it was.
I'll run the process and see what kind of growth occurs.