I notice our production .db file is 2.5 Gb

It should only be two hundred Mb. I know this because I unloaded
and recreated the db in a different environment.

Why is it so big. I noticed the indexes were fragmanted and had a lot
of pages, I rebuilt the indexes with "ALTER INDEX xxxxxx ON yyyyy
REBUILD, This reduced the no of index pages considerably.

However there was absolutely no effect on the size of the .db file,
it's still 2.5 Gb

Why is this, what can we do to make the database smaller, it seems to
about 10 times bigger than it should be.

The only way to regain space in a database is to unload it and then
rebuild it.

Your database is 2.5 GB because at some point in time we needed that
amount of space, or possibly but less likely someone ran ADD 2GB command
on your database.

Roshintosh wrote:

FWIW having a lot of contiguous free space in a database file is
usually a *good* thing. Growing a database in Windows will guarantee
it grows into fragmented space, no matter how recently the drive was
defragmented; in effect, defragmentation only affects allocated file
space. So, as more space is needed to store database data, the
defragmented pre-allocated database file will be more efficient than a
file which needs to grow.

Sometimes, folks complain that a giant file is hard to back up, and
hard to maintain on a small handheld device, but generally 2.5G is not
regarded as being very big these days.

Breck



On Wed, 23 Sep 2009 08:16:20 -0700 (PDT), Roshintosh
<roshintosh (AT) yahoo (DOT) co.uk> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com