Checkpoint

Checkpoint:

It is an event that occurs regularly in SQL Server depending either on setting of ‘recovery interval’ option or depending on occurrence of some particular events.

Checkpoint can be termed as a standard reference point for SQL Server. SQL Server commits all the transactions once checkpoint occurs also writes all dirty pages to disk.

When Checkpoint occurs?

·         If log file is 70% full. (Incase of SIMPLE recovery model).

·         If DB engine determines the time required to write dirty pages to disk is crossing the value set for ‘recovery interval’ option. (Incase of FULL/BULK-Logged recovery model).

·         While DB backup.

·         When following commands are executed:

·         Commit.

·         SHUTDOWN

·         All DDL Commands.

Command:

CHECKPOINT [duration (in seconds)]

This command can be used to trigger checkpoint manually. Duration can be specified if we wish to complete the event in some particular time if not specified by user SQL Server automatically determines time considering performance and the amount of dirty pages to be written.

Links …