Posted by davisutt on 07/29/07 15:54

On Jul 28, 4:55 pm, kmounkh...@gmail.com wrote:
> On Jul 24, 3:49 pm, davisutt <davis...@aol.com> 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.

[Back to original message]