Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We have 3 database spaces: central.db, sales.db, invoice.db. The invoice.db has grown quite large and we are looking to drop 50% of the records (90GB of data). Is it possible to rebuild just this database space to reclaim this disk space or will we have to do a full unload/reload of the entire database?

We are using SQL Anywhere v12..01.3942 on Windows Server 2008 R2.

Thanks, Brian

asked 15 Apr '14, 09:56

Brian's gravatar image

accept rate: 0%

This is not an actual answer, but...

What are the sizes of the three files?

The delete, unload and reload processes may be quite time consuming, compared with simply creating a new database and doing selective UNLOAD SELECT statements followed by LOAD TABLE on the new database to copy the data you want... rather than struggling with the data you don't want.

If the other two files are small compared with invoice.db, they may not be worth excluding from the process.

Note that the dbunload -nl option can be used to generate a reload.sql file with all the commands required to recreate and reload everything... without actually unloading any data.

AFAIK you would be on your own coding the UNLOAD SELECT statements to create the files used by the reload.sql file.

Also, this discussion assumes you will be using the dbunload command rather than Sybase Central (don't even think of using Sybase Central for any of this :), and that you understand and test the dbunload -nl, dbisql UNLOAD SELECT, dbinit and dbisql reload.sql commands beforehand.

(15 Apr '14, 10:32) Breck Carter

In addition, how many tables do reside on the larger dbspace? Are they heavily connected via FKs and the like? Are there other database objects residing there - indexes and/or materialized views?

IMHO, the following untested method might work (it's expected you have a working backup!):

  1. Unload these tables (say, with DBUNLOAD -t MyTable1, MyTable2, ...), i.e. both structure and data
  2. Make sure you can use the according reload.sql script and the extracted data to re-create the according database objects.
  3. Drop the existing tables etc. from the old dbspace.
  4. Drop the dbspace.
  5. Create a new dbspace with the same name and the desired size (ALTER DBSPACE ADD...)
  6. Use the script from DBUNLOAD (which will require some modifications, methinks) to re-create the according tables (and indexes) and reload the data.

In case you only have one single table on the dbspace, it would be easier to just rename that table, rename the dbspace, create a new dbspace with the according table and INSERT SELECT from the old table, and then drop the old objects.

Aside: I do not claim this is easier than a general reload.

(15 Apr '14, 11:10) Volker Barth

I am getting the impression that there is no built-in clean way to do this and since our database is involved in replication / synchronization, it probably creates some additional complexitities. I will test standard unload/reload and head down that path. Thanks for your feedback.

(17 Apr '14, 08:51) Brian
Replies hidden

Are you using both SQL Remote and MobiLink? Using SQL Remote implies you must protect the transaction log, and that implies special care must be taken with unload/reload as discussed in the Help.

If it was me, I'd try to live with a *.db with plenty of free space for repeated growth... it will be reused for new rows... rather than deal with the pain of unload/reload. Disk space is cheap. REORGANIZE statements can be used to gain the other performance benefits of unload/reload.

(17 Apr '14, 09:01) Breck Carter

I guess in both cases (SQL Remote and/or MobiLink), my suggestion to drop and re-create published tables is not recommended at all - that will usually break things... - Even a full unload/reload is something I would try to omit in such cases if possible, and otherwise I'd recommend very thorough testing.

So I'll certainly share Breck's point of view:)

(17 Apr '14, 09:28) Volker Barth

> in both cases (SQL Remote and/or MobiLink)

AFAIK, in the case of a database that plays no other role than as a MobiLink consolidated database, there are no restrictions on ordinary operations like unload, reload, drop-and-recreate, etc (with stress put on the word ordinary operations, there are certainly other things you can do to destroy synchronization :)

In particular, a MobiLink consolidated database has no physical representation of "published table"... think Oracle :)

Caveat: Trying to do some of those "ordinary operations" while the MobiLink server is running moves them out of the "ordinary" category into the "death wish" zone :)

(17 Apr '14, 09:34) Breck Carter

Thanks for the clarification, I was more thinking of a ML client...

(17 Apr '14, 09:39) Volker Barth
showing 3 of 7 show all flat view

You might suggest an enhancement to "move" a table to another dbspace, something like the fictional

ALTER TABLE [owner.]tablename MOVE TO location-string

Apparently, that would be an expensive operation, even more than REORGANIZE TABLE...

That way, you could move a table to a new dbspace and drop the old one afterwards - or could move it "home" to the system dbspace. Like REORGANIZE TABLE, it should not have effect on replication/synchronization as it would just move data physically, but not logically.

permanent link

answered 22 Apr '14, 14:56

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 22 Apr '14, 14:57

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Apr '14, 09:56

question was seen: 4,194 times

last updated: 22 Apr '14, 14:57