Automatic Checkpoint Procedure

Approximately once a minute, a checkpoint task checks each database on the server to see how many records have been added to the transaction log since the last checkpoint.

If the server estimates that the time required to recover these transactions is greater than the database’s recovery interval, SAP ASE issues a checkpoint.

The modified pages are written from cache onto the database devices, and the checkpoint event is recorded in the transaction log. Then, the checkpoint task “sleeps” for another minute.

To see the checkpoint task, execute sp_who. The checkpoint task usually appears as “CHECKPOINT SLEEP” in the “cmd” column:

fid  spid  status      loginame  origname  hostname         blk_spid  dbname
        tempdbname    cmd                block_xloid   threadpool
---  ----  ----------  --------  --------  ----------------  --------  ------
        ----------    -----------------  -----------   -------------------
. . .
0       2    sleeping      NULL      NULL                NULL       0  master
            tempdb     DEADLOCK TUNE              0    syb_default_pool
0       3    sleeping      NULL      NULL                NULL       0  master
            tempdb     ASTC HANDLER               0    syb_default_pool
0       4    sleeping      NULL      NULL                NULL       0  master
            tempdb     CHECKPOINT SLEEP           0    syb_default_pool
. . .

SAP ASE inserts a checkpoint record immediately after upgrading a user database. SAP ASE uses this record to ensure that a dump database occurs before a dump transaction occurs on the upgraded database.