I have a customer that is concerned about the size of a database file.

It has been consistent at about 700MB for a long time. We added about 50
columns (a single character) to a large table and saw the size increase to
about 1.5GB...which is a lot more than I would expect but I guess not
unreasonable.

The customer reports that the database has gone back to 700MB (the next
day...following a backup). Is that possible? Expected?

Thank you.

Sure. The database probably consists almost entirely of the one table so
the growth from about 700m to 1400m would be the checkpoint log. Space
in the checkpoint log cannot be reused until a checkpoint occurs and if
you are altering a table to add a column, you probably modified every
page in the table and checkpoints cannot be performed during an alter. A
copy of every modified page (and therefore I would guess almost every
page in your database) would have been saved in the checkpoint log. When
you shut down the server, the checkpoint log is truncated.

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

David DeRam wrote:

Thanks John. That makes sense.

We actually did the ALTER (adding the columns and default values) in our lab
and sent them the database and log file back.

The database then grew to 1.5GB after they received it and then went back to
700MB.

I would think that after we shut the database down (before delivering it to
them) that the checkpoint log would have been truncated. I'm having a hard
time trying to figure out why it would have jumped in size AFTER the
database was already shut down.

Do you have any ideas? They are hesitant to put it back in production and
currently have the system down.

Thank you.


"John Smirnios [Sybase]" <smirnios_at_sybase.com> wrote

Is there a large UPDATE that is being applied to the table when the customer
starts the database that will modify all of the rows, and thus cause the
checkpoint log to grow again? Using DBTRAN to examine the contents of the log
beyond the ALTER may give some clues.

SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




David DeRam wrote:

Thanks Bruce. We went down that path and examined the log file for the
operations that they ran after receiving the database.

From what we could tell it was just business as usual. (We know that the
database has never grown this way because it caused a server alert for the
first time...the alert was for a file that suddenly grows).

We applied that log file to the database that we sent and sure enough the
size did increase dramatically...and it went back down after shutting the
server down.

Since we added 50 char(1) columns to the table I'm wondering if a single row
now exceeds the page size. Do you think that exceeding the page size would
cause this behavior?

Thank you.

"Bruce Hay" <h_a_y_@_i_a_n_y_w_h_e_r_e_d_o_t_c_o_m> wrote

I would not expect row size to cause this behavior, but it's possible.

You might look at when checkpoints happened during the operations recorded in
the log. If checkpoints were prevented, e.g. by a backup, during a period of
heavy update activity, that could have caused the checkpoint log to grow.

You might also try applying the SQL from the translated log to see when the
growth happens. Break the file into several pieces, each ending on a
commit/rollback. Then apply each piece and record the file size. Checkpoints are
free to happen at different points than in the original scenario (when applying
the untranslated log), so you might not see the same growth.

SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




David DeRam wrote:

Are triggers defined in the database? If so, in addition to Bruce's
suggestion, try using dbtran -t to see the trigger actions. You might
have a trigger that is updating more rows than you expect.

-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

David DeRam wrote: