REORGANIZE TABLE vs DBUNLOAD by primary key

[Rachan Terrell]

REORGANIZE TABLE vs DBUNLOAD by primary key.....Which one is best to run?

We noticed that when we run REORGANIZE TABLE for the tables that are
fragmented our database size went from about 6 Gb to 10 Gb. Any one has any
idea why the size increased so much.

Thank in advice for your helps.

Best Regards,
Rachan Terrell

[Unknown]

Which versions and builds of SQL Anywhere are you running?
Also just as important what version of the software was used
when the database file was created! ?

Does the size shrink after shutting down and restarting the server?

Some growth can be expected but that seems like a lot. For
example in the 10.0.1 docs:

SQL Anywhere� Server - SQL Reference
> SQL Statements
REORGANIZE TABLE statement
. . .
"Since both forms of reorganization may modify many pages,
the checkpoint log can become large. This can result in a
increase in the database file size. However, this increase is
temporary since the checkpoint log is deleted at shutdown
and the file is truncated at that point."

shuttind down and restarting the server should clean some of
this up.

Running `dbinfo -u` should tell you how that space is being used.

"Rachan Terrell" <web2...@hotmail.com> wrote in message
news:48e3843d$1@forums-1-dub...

[Rachan Terrell]

Nick,

The version we are using is 10.0.1.3694. We have shut down the server and
restart it but the size still 10 Gb. The database file was create on version
10 build 3624.

DbInfo show.....

Total: 434278 table + 517095 index + 397282 free + 0 bit map + 191
other = 1348846 pages
Most of the table with alot of page have over 90% used there are alot of
index with about 70-80 % used. There is one index that have 159155 pages and
53 % used.

Thank you for your helps. The DbInfo command really helpful.

Rachan Terrell

"Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message
news:48e3b8d4$1@forums-1-dub...

[Unknown]

> DbInfo shows.....

So I take it that you are using an 8KB page size; seeing that 1.35 million
pages is ~10GB. Given that your large index that is only 53% used
might do better with an 4KB pages size ... but that is not a hard
recommendation.

A lot of the growth does seem to be have contributed to the free pages.
That may be possible if the database was initially highly fragmented in
it's largest tables tables. If you still have the source database you may
want to investigate table fragmentation and index density (see below).

> Thank you for your helps. The DbInfo command really helpful.

You are indeed welcomed. I've found that feature invaluable many
times over the past decade or so myself. Engineering seems to
agree since over there the years that feature has found it's way
into being a 'tab' to the database view in Sybase Central.

There is also numerours system stored procedures you might find
helpful as well, including:

sa_table_page_usage( ) - think dbinfo with the -u w\o the summary
sa_table_fragmentation( ) - which you may already be familiar with
sa_index_*( ) - multiple procedures here ... check out the density
one on your big index; the one with a high
number
of leaf pages.

"Rachan Terrell" <web2...@hotmail.com> wrote in message

news:48e3c482$1@forums-1-dub...

[Rachan Terrell]

Nick,

Thank again for the cool function that you reply. It's really great to know
these function. Hope you have a wounderful weekend. Again, thank so much.

Best regards,
Rachan terrell

"Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message

news:48e4d855$1@forums-1-dub...