Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - Database Administration » Maintaining Your Database » Backup and data recovery » Backup and recovery internals

 

Checkpoints and the checkpoint log

The database file is composed of pages: fixed size portions of hard disk. The checkpoint log is located at the end of the database file. Pages are added to the checkpoint log as necessary during a session, and the entire checkpoint log is deleted at the end of the session.

Before any page is updated (made dirty), the database server performs the following operations:

  • It reads the page into memory, where it is held in the database cache.
  • It makes a copy of the original page. These copied pages are the checkpoint log.
The database server reads page A into memory where it is held in the cache. Then page A is added to the checkpoint log.

Changes made to the page are applied to the copy in the cache. For performance reasons they are not written immediately to the database file on disk.

Changes made to page A are applied to the copy in the cache, now called page B. The changes to page A are recorded in the transaction log.

When the cache is full, the changed page may get written out to disk. The copy in the checkpoint log remains unchanged.

Page B is written to the database, but the copy of page A remains unchanged in the checkpoint log. The transaction log contains the changes made to page A since the checkpoint.

A checkpoint is a point at which all dirty pages are written to disk and therefore represents a known consistent state of the database on disk. Following a checkpoint, the contents of the checkpoint log are deleted. The empty checkpoint log pages remain in the checkpoint log within a given session and can be reused for new checkpoint log data. As the checkpoint log increases in size, so does the database file.

At a checkpoint, all the data in the database is held on disk in the database file. The information in the database file matches that in the transaction log. During recovery, the database is first recovered to the most recent checkpoint, and then changes since that checkpoint are applied.

The entire checkpoint log, including all empty checkpoint log pages, is deleted at the end of each session. Deleting the checkpoint log causes the database to shrink in size.

The database server can initiate a checkpoint and perform other operations while it takes place. However, if a checkpoint is already in progress, then any operation like an ALTER TABLE or CREATE INDEX that initiates a new checkpoint must wait for the current checkpoint to finish.

For more information about when checkpoints occur, see How the database server decides when to checkpoint.