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.

Please confirm that SQL Anywhere v17 does not reclaim unused space

3

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.

asked 14 Nov '18, 14:25

AlK's gravatar image

AlK
735●31●35●54
accept rate: 37%

2

The database file space that was previously used by the permanent table data becomes "free space" when rows are deleted. It can be reused for new rows in the same table (for partially-empty pages) or different tables (for entirely empty pages), but it cannot be "reclaimed" back to the OS.

The database file space may grow in size while many rows are deleted, because the checkpoint log (which includes the rollback logs containing all the deleted data) is stored at the end of the database file. This file space is recovered (reclaimed) when the server is shut down.

So they answer to your first question is "mostly yes, subtly no" :)

(14 Nov '18, 14:39) Breck Carter
Replies hidden
1

Question 2: Yes, but the database has to be shut down (which means the answer is effectively "No" for single-database servers).

Question 3: Dbunload is a tool that hides/optimizes the details. The underlying logical process is to (a) make a copy of the data, (b) stop the database, (c) delete the big file and create a small one, (d) start the database and (e) reload the data into the database.

From your end user's point of view, the database becomes unavailable... which may be why you're asking Question 3.

(14 Nov '18, 14:46) Breck Carter
2

FWIW, the checkpoint log pages are not fully freed when the database is stopped, a "typical amount" is preserved for the next checkpoints.

(14 Nov '18, 15:25) Volker Barth
One Answer:
active answersoldest answersnewest answerspopular answers
2

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.

permanent link

answered 15 Nov '18, 04:21

Volker%20Barth's gravatar image

Volker Barth
40.2k●361●549●822
accept rate: 34%

edited 15 Nov '18, 04:37

2

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

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

(15 Nov '18, 11:18) Justin Willey
Replies hidden
1

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".

(15 Nov '18, 15:58) Breck Carter

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.)

(16 Nov '18, 18:49) AlK
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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

learn more about Markdown

Question tags:

×10

question asked: 14 Nov '18, 14:25

question was seen: 1,625 times

last updated: 16 Nov '18, 18:49

Related questions

Mobilink client: temp files filling up disk

Freeing disk space by nulling nvarchar columns

Table Size vs Database Size

Overview in Sybase Central - How much disk space is enough to keep it happy?

Setting the location of temporary files

Why is sa_disk_space() only available to administrators?

Upgrading 9.0 to 16.0 on external disk

Gathering database space usage statistics without Sybase Central installed

Pre-allocate disk space prior to recovery