Types of SQL Server CHECKPOINTS

Types of SQL Server CHECKPOINTS : 

Automatic CHECKPOINT --

It is a background process in SQL Server and default, most common checkpoint. We have a recovery interval server configuration in the sp_configure command,

The default value for the recovery interval is set to zero, which means the target recovery interval is one minute.

Internal CHECKPOINT --

SQL Server controls these internal Checkpoint events. DBA cannot interfere in these operations.

SQL Server also issues internal SQL Server checkpoint based on a few key events in SQL Server. These events are as follows.

1) During database backup

2) Database Snapshots. It might be explicitly database snapshot or snapshot due to the DBCC CheckDB command

3) SQL Server also performs Checkpoint for a clean shutdown of SQL Services

4) Add\Remove database files using the Alter database command

5) Switching recovery models from FULL or Bulk-logged to Simple

6) In the simple recovery model, if the log file becomes 70% full

Manual CHECKPOINT --

We can also issue Manual Checkpoint in SQL Server using the SQL Server CHECKPOINT command. This command runs under the current database context only.

Indirect CHECKPOINT---

Indirect Checkpoint is the new feature starting from SQL Server 2012. It is the default SQL Server Checkpoint type from SQL Server 2016. In this mode, we can specify target recovery time for a specific database.

We can specify the target recovery time using the following Alter database command.

ALTER DATABASE [Database name] SET TARGET_RECOVERY_TIME = Duration_InSeconds;