cancel
Showing results for 
Search instead for 
Did you mean: 

Please confirm that SQL Anywhere v17 does not reclaim unused space

2,314

Hello,

Would someone confirm what what I believe to be the case which is that if a meaningful amount of data is deleted from a database the server does not "reclaim" that diskspace with a resulting reduction in the size of the database.

Also, I believe that it's possible to reduce the size of the database by running the dbunload utility.

  • Is this correct?

  • Can this be run without shutting down the server?

  • Is there any other way to do this?

Thank you.

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor

In addition to what been said by Breck, some more information:

  • The re-use of database pages is documented here for example - to cite:

Databases do not shrink if you delete data. Instead, any empty pages are simply marked as free so they can be used again. They are not removed from the database unless you rebuild it. Rebuilding a database can reclaim disk space if you have deleted a large amount of data from your database and do not anticipate adding more.

  • The re-claim of an "appropriate amount" for checkpoint log pages was introduced with 12.0.0 and documented as such:

Checkpoint log changes: In previous releases, when you stopped a database, SQL Anywhere completely truncated the checkpoint log. In version 12, a history of the checkpoint log usage is maintained in the database and is used to determine an appropriate size for the checkpoint log for the next session.
Maintaining the checkpoint log across sessions avoids the overhead of allocating it the next time the database is started and avoids file fragmentation that can occur when files are extended. Database files will now be larger when the database is shut down than they were in previous releases, but the additional space is reused for the checkpoint log the next time the database is restarted.

  • To check the amount of free space, see that discussion.

  • When a rebuild is not feasible because the database cannot be shut down, a REORGANIZE TABLE statement may help to decrease table fragmentation. That will not reduce the total file size but it may lead to more free pages and therefore prevent or minimize further file growth.

justin_willey
Participant

There is this suggestion in the docs for minimising downtime when rebuilding:

http://dcx.sap.com/index.html#sqla170/en/html/818b8ad06ce21014bcccf2da26300405.html

Breck_Carter
Participant

That suggestion includes dbbackup -wa which (I think) is the same as BACKUP WAIT_AFTER_END which is documented with this warning: "Use this clause with caution as new, incoming transactions can cause the backup to wait indefinitely".

0 Kudos

Thank you to Breck, Volker and Justin. (Breck, you were the first to reply but I don't see any way to mark your reply as an answer.)

Answers (0)