What is a SQL Server checkpoint?

A SQL Server checkpoint is the process of writing all dirty datafile pages  out to disk. A dirty page is page that has changed in memory (buffer cache) since they were read from disk or since the last checkpoint. This is done regardless of the transaction that made the change.

SQL Server uses a protocol called Write Ahead Logging (WAL) and it is this process that writes all log records describing a change to the data page to disk before the actual page is written to disk.

Checkpoints can occur concurrently on any number of databases on an instance.

How do database checkpoint occur?

Before a backup the database engine automatically performs a checkpoint, this ensures that all database changes are contain in the backup.

You issue a manual checkpoint command, a checkpoint is the run against the database in use

SQL Server is shutdown. If the checkpoint is skipped (SHUTDOWN WITH NOWAIT) the restart will take much longer

ALTER DATABASE is used to add or remove a database file.

If you change  the recovery model from bulk-logged to full or full to simple recovery model.

If your database is in full or bulk logged recovery mode checkpoints are run periodically as specified by the recovery interval server setting

In simple recovery checkpoints are run when the log becomes 70% full or based on the recovery interval setting, which ever comes first.

SQL server Recovery Interval

A note on the recovery interval – the time between checkpoints is determined by the recovery interval and the number of records in the transaction log. The recovery interval is set for an entire instance of SQL Server. The value represents the number of minutes that you choose to allow SQL server for automatic recovery. SQL server uses an algorithm to determine when to perform the next check point. The algorithm will create checkpoint to ensure that in the event of system failure the recovery will not take longer than the time specified in your recovery interval.