Writes all dirty pages (pages that have been updated since they were last written) to the database device.
checkpoint [all | [dbname[, dbname, dbname, ........]]
Writes all dirty pages in the current database to the database device, regardless of the system checkpoint schedule:
checkpoint
Use checkpoint only as a precautionary measure in special circumstances. For example, Adaptive Server instructs you to issue the checkpoint command after resetting database options.
Use checkpoint each time you change a database option with the system procedure sp_dboption.
You can specify the database or databases to run checkpoint.
If you want checkpoint all to run against all databases, including system and temp databases, you have to have the sa_role or oper_role.
If you do not have the sa_role or oper_role, the checkpoint all will only run against those databases you own.
Checkpoints caused by the checkpoint command supplement automatic checkpoints, which occur at intervals calculated by Adaptive Server on the basis of the configurable value for maximum acceptable recovery time.
The 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 Adaptive Server.
The automatic checkpoint interval is calculated by Adaptive 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 the system procedure sp_configure.
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.
ANSI SQL – Compliance level: Transact-SQL extension.
checkpoint permission defaults to the Database Owner. It cannot be transferred.
System procedures sp_configure, sp_dboption
