At 11:51 PM 14/08/2006, you wrote:

>I have a database which grew about 800 MB in the last one and a half
>month. This means it doubled in size. I try to understand what is
>taking up the space but can't figure it out.
>
>We added a table which logs every changed row of data from an original
>table. It does so with the aid of a few triggers. This table is
>expected to grow. When I look at the amount of pages in data on this
>database, most (about 80%) of the growth is in this table. I figure if
>I multiply the amount of pages with th pagesize (4096) I get an
>estimate of the size the database should have grown. In that case the
>table grew around 60 MB, but the increase in filesize is nearly 800 MB.

When I multiply the Pages Allocated figure from Show Database in isql
by the page size of the database I get exactly the same numbers as
the on-disk size of the database file.

Where are you getting your numbers from?

>Does anybody have a clue where I can find the remaining 700+ MB?
>
>Note: Backing up and restoring the database doesn't make the file any
>smaller. The backup also grew from around 500 Mb to 1300 MB

If the database is fairly stable, the size after restore is a pretty
good indicator of the low water mark wrt database size, since it will
be in a state whereby each table, index, etc. is "packed", i.e. you
don't have any objects with a lot of spare space available on
pages. The database engine fills each page to 80% capacity; objects
are not mixed on pages. With small record structures there can be
multiple records on a page; with records larger than slightly less
than half the DB page size, each record will be spread across n pages
and each new record version will need n new pages allocated from the
filesystem if there are not n empty pages available already inside
the database.

gstat -a will give you a snapshot of pages allocated and fill
percentages, table by table. You could work out a way to keep stats
from this, e.g. by recording the numbers right after a restore and
then periodically afterwards, to see where your main growth areas
are. The differences between one freshly-restored version and
previous ones should provide a pattern from which you can estimate
overall growth.

./heLen