checkpoint

Description
            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
            Writes all dirty pages in the current database to the database device, regardless
            of the system checkpoint schedule:

                 checkpoint

Usage
            •    Use checkpoint only as a precautionary measure in special circumstances.
            •    The sp_dboption system procedure automatically defaults to using
                 checkpoint when you change a database option.
            •    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.

            Automatic checkpoints

            •    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.
            •    You can configure Adaptive Server with multiple checkpoint processes.
                 This allows Adaptive 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.