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, 14:25

AlK's gravatar image

AlK
660253249
accept rate: 33%

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, 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, 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, 15:25) Volker Barth

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, 04:21

Volker%20Barth's gravatar image

Volker Barth
32.6k328476696
accept rate: 32%

edited 15 Nov, 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, 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, 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: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

Question tags:

×9

question asked: 14 Nov, 14:25

question was seen: 93 times

last updated: 16 Nov, 18:49