Log files grows indefinitely and causes ETL to fail

Hello;

I 'm feeding my warehouse through SSIS. When I run a package that does full load from staging to the fact table(which takes 7hrs at least) the log runs out of space. I've added space to the log drive and created a job that shrinks the drive every 10mins but still it does'nt seem to solve the problem.

I'm having this problem since I created a primary key column in the fact table. Can anyone suggest if they have encountered this problem before

thanks

Rok

Hi,

Might be worth raising this in the SSIS forum, but have you tried reducing the number of events you are logging. I think by default it logs all events, I'm sure most of the events are redundant and you can reduce the number of outputs into your log by simply turning them off.

Hope that helps

Matt

Thanks Matt;

Could you tell how can I reduce the number of events that's been logged Meaning- how can I turn it off

Rok.

Hi,

If you open up your project in BIDS, goto

SSIS

-Logging...

And in here you can create different providers for logging, in the Details tab you can set up which events to capture. There are a few important ones, such as OnError and OnWarning. Probably some other important ones, but it's been a while and I can't quite remember what they all do now. If you want to reduce the size more you could just output specific things like SourceName and MessageText. Some of the other things are fairly useless if you are using a sql job to run it, as they will just be repeated on everyline over and over again. All depends on what you feel is nessecary to capture.

Hope that helps

Matt

Matt;

Thanks for explaining the logging in SSIS. I'm still dubious on which option to choose.

1. Changing the Logging properties in SSIS-(As you've suggested)

or

2. Changing the recovery model from FULL to SIMPLE and swapping it back once I complete the full warehouse load.

I think both provides a similar kind of functionalities, though your option sounds much more flexible. What do you think

thanks,

Rok

Hello! You should always choose simple recovery for you DW database.

Full recovery is for transactional or OLTP systems.

HTH

Thomas Ivarsson

Thomas;

Thanks for your response. I would be appreciate if you can help me understand why DW database needs to be in SIMPLE mode, Is it all because of LOG FILES

thanks.

Rok

Hello! You do all updates and inserts in a DW in batches, normally by night.

You do not need to restore a DW database down to the last transaction because you do not have updates in a DW except in batches.

If you do a nightly update during 01:00 to 02:00 AM you can make a full or incremental backup after that in order to have a working copy of your DW. After that no updates or inserts will occur and you do not have to log transactions.

A DW database is only for quering by users.

HTH

Thomas Ivarsson