Problem solve Get help with specific problems with your technologies, process and projects.

How are checkpoints used in Sybase?

Please explain how "checkpoints" are issued in Sybase. How does a checkpoint affect the log, cache, data and recovery?

A checkpoint process is an integral part of database logging and recovery in Sybase and indeed other database engines. The operation itself is known as "checkpoint." To elaborate on this we need to understand the logging and recovery process in Sybase.

In Sybase -- or to be more precise, in Adaptive Server Enterprise (ASE) -- the recovery process for every database is accomplished by means of the so-called transaction log. This transaction log is a database-specific system table called "syslogs." The syslogs table contains a sequential (serialized) list of all modifications to every object in that database, as well as any information required to maintain data integrity.

The database transaction log is:

  • Recommended to be created on a device separate from data devices
  • Shared by all users of that database
  • written first (i.e. a write-ahead log)
  • A crucial component in database recovery and integrity

Note that for every user, ASE allocates a "user log cache" (ULC) of 1 page size. Let us assume that our page is 2K. So ASE allocates 2048 bytes of memory for every connection. ASE uses this cache (area of memory) to buffer the user transaction log records, which reduces the contention at the end of the transaction log.

A commit by a user causes ULC to be flushed to the cache version of the log and the cache version of the log is force written to syslogs table. A checkpoint writes both the log records for all users of that database (flushes the ULC for each user of that database to the syslogs table), plus writing dirty (modified) data buffers to disk. It is essential that a checkpoint forces the flushing of log records from cache to syslogs table. This makes sense in that if we flushed the modified data buffers to disk and do not flush the log records to disk and the system crashed, we would have modified records on disk with no log information associated with them. We need to flush the log buffers before writing modified data buffers so that the database integrity from a transactional point of view is maintained.

At recovery time (after server goes down and restarted), the last checkpoint time for each database is examined. Committed transactions that have not been written to the data area after the last checkpoint are rolled forward into the data. This is called roll forward. On the other hand, uncommitted transactions are rolled back. This is called rollback.

Now, moving to checkpoint itself. As mentioned above, a checkpoint writes both the log records for all users of that database to the syslogs table, plus all the modified data buffers for that database to disk. Strictly speaking, for the server to guarantee data integrity, it needs only to make certain that the log is written to syslogs. However, if enough transactions pile up in syslogs without being recorded on the data disk as well, after server goes down, it will take ages to recover all the committed transactions. So we need the checkpoint process to get data updated while the server is running and ensure that the committed data is available to the users of the database.

A checkpoint occurs under the following conditions:

  • The owner of database (dbo) issues the checkpoint command
  • The server runs out of data buffer space and requires additional space
  • The server recovery interval has been exceeded
  • A shutdown process is initiated by the DBA which checkpoints every database
  • A recovery process is completed for a database
  • A database is loaded from a backup

There are configuration parameters in ASE which affect the recovery interval for each database and also fast recovery, concurrent recovery, etc.

The automatic checkpoint process itself is handled by the ASE's background process (as seen in sp_who) with the column heading and value: cmd, 'CHECKPOINT SLEEP' respectively. This process wakes up every minute and examines each database in turn. Based upon the amount of data accumulated in the transaction log for each database since the last checkpoint, this process determines whether it will take longer than the recovery interval (default is set to 5 minutes in ASE 12.5.3) to restore the database. If so, it issues an automatic checkpoint. Also it is worthwhile noting that if the database option "truncate log on checkpoint" is set, this process prunes the transaction log for those transactions that have been committed and are now reflected in the data.

Dig Deeper on Linux servers