Very Computer

Does anybody have an recommendations on the best way to size the
transaction log for a database? I typically allocate 25% of whatever I
have allocated for data to my log. So if i I have a 10GB database I
would have a 2500MB log.  I would also expand my log everytime I exand
my database to keep it sized at 25% at all times.

ps - we are using repserver.

We had one db that was 2+ gig and had a 125MB log.
--
Pablo Sanchez              | Ph # (415) 933.3812        Fax # (415) 933.2821

===============================================================================
I am accountable for my actions.   http://reality.sgi.com/pablo/Sybase_FAQ

peter kuo
Perot Systems

: Does anybody have an recommendations on the best way to size the
: transaction log for a database? I typically allocate 25% of whatever I
: have allocated for data to my log. So if i I have a 10GB database I
: would have a 2500MB log.  I would also expand my log everytime I exand
: my database to keep it sized at 25% at all times.

I think that the size of the log is more related to the update activity
on the database than to its size. I have a 1.6 GB database with a 30 MB
log that has less updates than a 30 MB database (also with a 30 MB log).

During the daytime our logs are dumped every hour, unless a threshold
is reach which happen very infrequently. Looking at the size of our
dumps, a 30 MB log is sufficient for our databases.

Of course if you have big transactions that do things like
insert ... select ... from, where 1000nds of tuples are selected
you'll need more (not on an OLTP system of course :-)).
It all depends on what you want to do.

Luc.

+-----------------------------------------------------------+
| Luc Van der Veurst            ISUG Enhancements Co-Chair  |
| Academic Hospital, VUB        Voice : 32 - 2 477 69 80    |
| Laarbeeklaan 101              Fax   : 32 - 2 477 69 75    |

+-----------------------------------------------------------+
Next ISUG Conferences : Sydney, Australia 10-11 February 1997
                        Bangkok, Thailand 13-14 February 1997

Wendy,

We start of with a log allocation of 20-25%. But this entirely depends on the
type of activity on that particular database - Whether it is entirely
OLTP, or Batch or Mixed system.  If OLTP how many concurrent users on the
system.  If Batch what is the batch activity like size of files processed
in batch processes. If it is mixed environment,  then 20% is a very good
start.  Now after that the log utilization is monitored for a while
and then adjusted accordingly (upward ofcourse). Whatever the size
unless transactions are handled optimally (eg correct autocommit value in PB
etc) any size log can run out of steam.

-Prasad

Wendy,
        What kind of backup strategy are you using?  If you don't backup your
transaction log dump, you can setup activate truncate on checkpoint.  With
this option, you can have a smaller log.

Minh

Quote:>During the daytime our logs are dumped every hour, unless a threshold
>is reach which happen very infrequently. Looking at the size of our
>dumps, a 30 MB log is sufficient for our databases.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Database Administrator                    Phone:  (301) 838-0200
The Institute for Genomic Research        FAX:    (301) 838-0208
9712 Medical Center Drive
Rockville, MD  20850
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

If you only rely on thresholds to dump the transaction logs, you lose
the ability to reload a database up to an approximate point in time.  If
you dump every hour, *plus* enable thresholds, you can sleep much better
knowing the log has the best chance of not getting overrun (threshold)
and knowing you can recover a database to at least a given hour of a any
day.

--
Bob Lunney              | Building tomorrow's legacy
BSG-Cary                | systems today. (tm)

(919) 461-6261          | the dark.  -- Dwight Moody

Mike