Approximately once a minute, the 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, Adaptive Server 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 . . .