Skip to Content
Aug 23, 2016 at 02:52 PM

How big must your transaction log be



i would like to hear from some other people about their opinion about the size of the transaction log

Our general rule, we set the transaction log at a certain size (seperate device of course), so that the system could run for a 10-12 hours without completley filling up. We configure thresholds so that a transaction dump is periodicly taken and a schedule is fixed transaction every hour just to be sure.

But for the last couple of years as our ASE database is growing and procedures are getting bigger and bigger, it is now impossible to execute an unlogged transaction on a very popular table. Every object (stored procedure, trigger, function) is recompiled when executed giving very much transaction log in a very short time. As we need to perform a 'dump database' that takes up 30 minutes, the 2 GB transaction log is not enough (our DB size is 110 GB with a 240MB transaction every hour).

So if i double our transaction log to 4GB, is there a downside of doing this (space on the disk is not an issue)

i thought i once read some time ago that the 'recovery time' would be bigger, but i'm not sure what that means exactly.

Give me your thoughts please...