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.
Thank you. asked 14 Nov '18, 14:25 AlK |
In addition to what been said by Breck, some more information:
answered 15 Nov '18, 04:21 Volker Barth 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
|
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" :)
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.
FWIW, the checkpoint log pages are not fully freed when the database is stopped, a "typical amount" is preserved for the next checkpoints.