Checkpoint Process

Checkpoint Process:
It scans the buffer cache periodically and writes any dirty data pages for a particular database to disk.

Difference between checkpoint and lazywriter :
checkpoint process is never puts buffers on the free list but lazywriter  puts buffers on the free list.

The only reason of a checkpoint process is to guarantee that pages written before a certain time are written to disk.So that it keeps minimum dirty pages in memory and length of time SQL Server requires recovery of a
database after a failure is kept to minimum.

When Checkpoint occurs ?

It occurs automatically at regular intervals but can be requested manually.
The log is getting full and the database is in autotruncate mode. A checkpoint is triggered to truncate the transactional log and free up space but if no space can be freed up because of long running transactions, no
checkpoint occurs.
When recovery time is estimated to be longer than recovery interval a checkpoint is triggered.
When an orderly shutdown SQL server, a checkpoint is issued on every database on the instance.
When a backup is taken.
Database files have been added or removed by using ALTER DATABASE

Permissions required for checkpoint : Members of the SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR

Note :The frequency of checkpoints in each database depends on the amount of data modifications made, not on any time-based measure. A database used primarily for read-only operations will not have many checkpoints
The recovery interval option controls when SQL Server issues a checkpoint in each database. Checkpoints are done on a per-database basis.
When a checkpoint occurs SQL Server writes a checkpoint record to the transactional log, which lists all the active transactions.
A recovery Interval setting of 1 means that checkpoint occurs about every minute.
When you install SQL Server by default the recovery interval value is set 0. It indicates SQL Server chooses an appropriate value dynamically.
The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database