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.On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:
Quote:
Originally Posted by
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|||On Jul 28, 4:55 pm, kmounkh...@.gmail.com wrote:
Quote:
Originally Posted by
On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:
>
>
>
>
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
Main DW: DwSQL
Staging Area: DwLoadAreaSQL
>
Quote:
Originally Posted by
The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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.
No comments:
Post a Comment