checkpoint

Writes all dirty pages (pages that have been updated since they were last written) to the database device.

Syntax

checkpoint [all | [dbname[, dbname, dbname, ........]]

Examples

Usage

There are additional considerations when using checkpoint:
  • You can use checkpoint with an archive database, however, the checkpoint process does not automatically checkpoint an archive database.

  • Use checkpoint only as a precautionary measure in special circumstances.

  • sp_dboption automatically defaults to using checkpoint when you change a database option.

  • You can specify one or more databases to run checkpoint.

Automatic checkpoints:
  • Checkpoints caused by the checkpoint command supplement automatic checkpoints, which occur at intervals calculated by the SAP ASE server on the basis of the configurable value for maximum acceptable recovery time.

  • checkpoint shortens the automatic recovery process by identifying a point at which all completed transactions are guaranteed to have been written to the database device. A typical checkpoint takes about 1 second, although checkpoint time varies, depending on the amount of activity on the SAP ASE server.

  • The automatic checkpoint interval is calculated by the SAP ASE server on the basis of system activity and the recovery interval value in the system table syscurconfigs. The recovery interval determines checkpoint frequency by specifying the maximum amount of time it should take for the system to recover. Reset this value by executing sp_configure.

  • You can configure the SAP ASE server with multiple checkpoint processes. This allows the SAP ASE server with multiple engines to checkpoint tasks more frequently, thereby shortening the automatic recovery process.

  • If the housekeeper task can flush all active buffer pools in all configured caches during the server’s idle time, it wakes up the checkpoint task. The checkpoint task determines whether it can checkpoint the database.

    Checkpoints that occur as a result of the housekeeper task are known as free checkpoints. They do not involve writing many dirty pages to the database device, since the housekeeper task has already done this work. They may improve recovery speed for the database.

See also sp_configure, sp_dboption in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for checkpoint differ based on your granular permissions settings.

SettingDescription
Enabled
With granular permissions enabled:
  • To execute checkpoint on a particular database you must be the database owner or have either the checkpoint or own database (on the database) privilege.

  • To execute checkpoint all, you must be the database owner of all applicable databases or have either the checkpoint any database privilege or own and database privilege. Otherwise checkpoint all runs against those databases in which you have permission to run checkpoint.

Disabled
With granular permissions disabled:
  • To execute checkpoint database, you must be the database owner or be a user with any of these:
    • sa_role, or,

    • replication_role, or,

    • oper_role

  • To execute checkpoint all, you must be the database owners of all applicable databases or a user with any of these:
    • sa_role, or,

    • replication_role

Otherwise, checkpoint all only runs against those database you own. .