Log Space Usage

From: Karl & Betty Schendel 
Subject: Re: Log file full during an update from

At 6:41 PM +0200 9/3/99, Enrico Ben wrote:
>Last week I was updating a table with 72880 rows (600 * 72880 = 43728000
>bytes)
>with the following statement.
>
>The transaction was rolled back, because of transaction log full.
>I was the only person working on that database (and was the only database in
>that installation on a Digital Alpha).
>
>The log file is 256 MB (in my opinion big enough).
>
>Looking the IPM log file header, there was something like this
>
> +++++++++----------------->
>...My question is, why I need more than 256 meg to log an update of 72000
>bloody lines,


Because the space reservation for the CLR's (the ++++ stuff) is a function
of the number of rows affected, not the size of those rows.  The space
needed for the update log records (the -----) is a function of both the
number of rows affected and their size.  If you want to see the numbers,
do a SET LOG_TRACE and do an update that hits just a couple rows.

>when I'm sure that I can do the same thing on an M$Access database in less
>time?
>
>Secondly, why is needed the ++++ space ?

Because the recovery system is designed to undo operations instead of just
slapping old pages back into place, like 6.4 did.  Thus less log space is
*written* (although more might be *reserved* in some cases) than 6.4.
When you undo, you have to make sure that you repeat history first so as to
get the database into the exact state to undo from.  So when you start undoing,
you have to remember what you undid, so that if the system crashes, you
can redo only that which was not undone before continuing the undo.
Which means that you have to log the undo's, via CLR's (Compensation
Log Records).  Which means that you had better have the space to log the
undo or you would be hosed.  which is what the +++ is for.

>Maybe it was better to modify to heap the table before the update?

Probably not, unless you were updating storage structure keys.  If you were
updating a (say) btree key, it becomes a delete/reinsert instead of an update,
which requires even more log space.

However I see from your message that the table being updated was compressed.
It's quite likely that the update caused rows to move.  Not only is this
expensive, but if you had secondary indexes, they all had to be updated as
well, causing YET MORE logging.

Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise

Past President, North American Ingres Users Association
past_president@naiua.org
Ingres Q & A
Back to William's Home Page
Email William