There are four different types of checkpoints:
Internal checkpoints occur during some SQL Server operations, such as starting database backup or creating a database snapshot.
Manual checkpoint occur manually, as the name indicates, when users trigger them with the CHECKPOINT command.
Historically, SQL Server used automatic checkpoints, with the recovery interval controlled at the server level. The checkpoint process wakes up once or few times each recovery interval and flushes dirty data pages to disk.
Starting with SQL Server 2012, you have another option: indirect checkpoint. With this method, SQL Server tries to balance I/O load by executing checkpoints much more frequently – in some cases, even