Log files growing out of control

[jeffmoore64]

Hi.
I have a db that has the following settings:
-truncate on check pt. on
-recovery mode simple
-it is not published for replication
-auto shrink false
-auto close false
-auto create stats true
-auto update stats true

We are loading the db using an ETL tool called Data Inegrator. It has a setting called Commit records:1000
That will commit the records after every 1000 records.

The problem we are seeing is the log file continues to grow. It is currently at 123GB. Auto grow is set for 10%.
It is now taking 35 minutes to grow the log, and increasing by about 4 minutes 2 minutes each grow cycle.

we are only getting about 6 minutes worth of record processing between autogrow cycles.

What we can't figure out is why, with the settings described above, is the log file growing.

Any help here?

TIA

 

[ptheriault]

The problem is you are using '%' for auto grow and not a set amount. 10% of 123GB is very large. It most likely doesn't need to grow that much for the transactions you are running. Change the autogrow to something like 500 MB.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

The log file has grown from 40Mb to 135Gb in the last 15 hours...... I wish I knew where to set the top end. If I did I'd just grow the thing manually.

Some thing is causing the transaction log to not truncate on the check points.

 

[ptheriault]

Are you sure your importing is committing every 1000 records? You must have a long running transaction.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[RobertT687]

Do you know if there are any triggers on the table(s) being loaded that may be generating additional logged tranactions?
Is there a clustered index on the target table(s)? In my experience, that can also generate a lot of logged transactions.