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:
spid status loginame hostname blk dbname cmd ----- ---------- ---------- -------- --- ------- ---------------- 1 running sa mars 0 master SELECT 2 sleeping NULL 0 master NETWORK HANDLER 3 sleeping NULL 0 master MIRROR HANDLER 4 sleeping NULL 0 master HOUSEKEEPER 5 sleeping NULL 0 master CHECKPOINT SLEEP
