Database pages are freed when all records on the page are deleted. When a database page is freed, it becomes available for reuse, but it cannot be removed from the file. However, future INSERT and UPDATE statements can use the freed pages.
Whenever modifications such as inserts, updates, or deletes are made to the database, entries are added to the rollback log, which is stored within the system dbspace. If many of these operations are performed before a commit is executed, the rollback log can become very large and may increase the size of the database.
The checkpoint log is stored at the end of the system dbspace. When the database server shuts down, the checkpoint log is truncated and the system dbspace shrinks. Pages that were freed by DELETE or TRUNCATE operations remain within the database file for future reuse and cannot be removed from the file.
If the size of your database file is increasing, or is not decreasing as expected:
Execute COMMITs frequently if you are using INSERT, UPDATE, or DELETE statements. Pages allocated for the rollback log are freed for reuse in the system dbspace when a COMMIT is performed.
Execute CHECKPOINTs occasionally when you are using UPDATE or DELETE statements, or if you are using INSERT statements and large indexes are involved. Pages in the checkpoint log become available for reuse by the checkpoint log after each checkpoint.
Execute TRUNCATE TABLE, which can result in page-level deletes. In these cases, copies of the pages do not need to be added to the checkpoint log and individual row-level operations do not need to be added to the rollback log. Pages freed by TRUNCATE TABLE are only reusable after the next checkpoint. TRUNCATE TABLE results in page-level deletes when the following conditions are true:
Rebuilding the database can decrease the size of the database because the rebuilt database has fewer free pages.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |