There are four different types of checkpoints:
Internal checkpoints
Internal checkpoints occur during some SQL Server operations, such as starting database backup or creating a database snapshot.
Manual checkpoints
Manual checkpoint occur manually, as the name indicates, when users trigger them with the CHECKPOINT command.
Automatic checkpoint
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.
Indirect checkpoint
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
continuously.
No responses yet