Need to defrag SQL Anywhere 5.5.04 database

[Louis_J._Ciocci]

I have a 2+Gb database that needs to be defragged. The database runs 24x7,
as it is the back end to our web site and our employees are using it 24x7
as well. I run nightly online backups of the database.

I have unloaded/reloaded the database in the past, before our web site was
up
and running, and the whole process would take (at worst) 2 days to
complete. I
can't afford to be down for 2 days any longer. I am toying around with the
idea of installing another database server so that I can perform an ordered
reload
on that machine, and make that machine my production database environment.
I would
use this scheme to swap the production and test database environments when
I need to perform this defrag operation.

I am looking for alternatives to this, or at least some confirmation that
my
plan is sound. Has anyone any other ideas for performing a defrag with
minimal downtime?

Regards,
Louis J. Ciocci
Chief Technology Officer
Decision Strategies, Inc.

[Breck Carter]

How will you ensure the data is up to date when you switch over?

Is there a small number of problem tables? If so, then perhaps an
in-place copy, from old table to new on pre-allocated DBSPACEs to
reduce future fragmentation, followed by renaming the tables... the
app would only be offline long enough to rename the tables.

The problem remains, though... making sure all the recent updates have
been copied to the new database, or new table, when the switch
happens. This might be facilitated by a DEFAULT TIMESTAMP column on
the rows to be copied.

Breck

Breck [TeamSybase]
www.bcarter.com
bca...@bcarter.com

[Georgi Enchev]

I am planning to do something similar, and here's what I came with:
Create a remote user and subscribe it to all relevant tables, make an
offline backup, start the subscription and put the db online again. After
performing the reload on the copy, make the remote setup, have it apply all
the messages for it, stop the original from being used from outside, make
sure the copies are in sync, and finally replace the db.
I haven't tried it, but it ought to work.

Georgi

<Louis_J._Ciocci> wrote in message
news:7609B3D088A5AA78005166D1852569DE.005166EC852569DE@webforums...

[Bruce Hay]

The following should work:
1) using DBBACKUP -r, create a backup of the database and log and rename the
log
2) rebuild the backed-up database on another machine
3) do another DBBACKUP -r on the production server to rename the log
4) run DBTRAN on the log from step 3 and apply the transactions to the
rebuilt server
5) you now have a rebuilt database that contains all transactions up to the
end of the backup in step 3
6) shut down the production server and make copies of the database and log
7) copy the rebuilt database onto the production server
8) DBTRAN the log from step 6. This should be a relatively small file.
9) start the server on the rebuilt database, but don't allow users to
connect
10) apply the transactions from step 9
11) allow users to connect

I'd recommend doing a practice run of steps 1-5 to work out any problems and
determine the times required for each step. You may also want to save copies
of your files at various points. It's important that any other scheduled
backups not rename the production database's log; if that is done, you will
need to apply the transactions from these renamed logs to the rebuilt
database in the correct order.

[Louis J. Ciocci]

Hello Breck,

I understand what you're trying to do here, and it's not bad. I have one
question, though: What do you do with the old dbspace and tables? Part of the
problem I ran into in performing the rebuild was deleting the tables and
recreating the dbspaces - it took an awful long time to process.

Regards,
Louie

[Louis J. Ciocci]

Hello Bruce,

Thanks for the info. With regard to the rebuild, let me pose another question:
I have several tables in my database that have LONG VARCHAR columns. When I ran
the DBUNLOAD on these tables in the past (not sure what version of the DB I was
running), these columns would be truncated to 32K. Would you happen to know if
that's still the case with version 5.5.04?

[Breck Carter]

I like Bruce Hay's solution a lot better than mine.

Having said that, you may want to pre-allocate a lot of contiguous
space for the new database, and perhaps use dbspaces too. With Bruce's
solution you are starting over with a new database so
deleting/recreating is not an issue. Preallocation may prevent future
fragmentation.

Having said THAT, isn't continued 24by7 availability your biggest
concern, not how long some background or offline process takes?

Breck

Breck [TeamSybase]
www.bcarter.com
bca...@bcarter.com