Checkpoint in SQL Server
Checkpoint is an internal process that writes modified pages from
Buffer Cache to Physical disk, apart from this it also writes the log records
from log buffer to physical file. Writing of such pages from buffer cache to
data file is also known as Hardening of modified pages. It is a dedicated
process and runs automatically by SQL Server at specific intervals. SQL Server
runs checkpoint process for each Database individually.
Checkpoint helps to reduce the recovery time for SQL Server in the
event of unexpected shutdown or system crash and system Failure.
In SQL Server there are four types of Checkpoints:
Automatic: The most common checkpoint which runs as a process in
the background to make sure SQL Server Database can be recovered in the time
limit defined by the Recovery Interval – Server Configuration Option.
Indirect: newly added to SQL Server 2012 runs in the background
but to meet a user-specified target recovery time for the specific Database
where the option has been configured. Once the Target_Recovery_Time for a given
database has been selected this will override the Recovery Interval specified
for the server and avoid Automatic Checkpoint on such DB.
Manual: runs like any other T-SQL statement, once you issue
checkpoint command also run to its completion. Manual Checkpoint runs for your
current Database Only. You can also specify the Checkpoint_Duration which is
optional, this duration specifies the time in which you want your checkpoint to
complete.
Internal: As a user you can’t control Internal Checkpoint. Issued
on specific operations such as:
1. Shutdown initiates a Checkpoint operation on all databases
except when Shutdown is not clean (Shutdown with nowait)
2. If the recovery model gets changed from Full\Bulk-logged to
Simple.
3. While taking Backup of the Database.
4. If your DB is in Simple Recovery model, checkpoint process
executes automatically either when the log becomes 70% full, or based on Server
option-Recovery Interval.
5. Alter Database command to add or remove a data\log file also initiates
a checkpoint.
6. Checkpoint also takes place when the recovery model of the DB
is Bulk-Logged and a minimally logged operation is performed.
7. DB Snapshot creation.
