Database Checkpoint in SQL Server

SQL Server database engine does not directly write data page modifications in memory [buffer cache] to the disk. Instead it issues database checkpoint. Each time while database engine issues checkpoint, it writes modified data pages [dirty pages] and its transaction log information from memory to disk. And also it records checkpoint information to transaction log.

Type of Database Checkpoints

1.   Automatic Checkpoint

a.  It is issued automatically from the background based on the [recovery interval] defined.

b.   [Recovery Interval] parameter is specified in server level.

c.  Default [recovery interval] value for automatic checkpoint is 0(zero) in which target recovery interval is 1 minute.

d.   Query to define the [recovery interval] 

                    EXEC SP_CONFIGURE '[recovery interval]','seconds'

2.   Indirect Checkpoint

a.   It is issued from the background based on the [TARGET_RECOVERY_TIME] defined in the database.

b.   [TARGET RECOVERY TIME] parameter is specified in database level.

c.    Default [Target Recovery Time] value is 0(zero) which causes automatic checkpoint.

d.   Query to define the [TARGET RECOVERY TIME]

ALTER DATABASE [database name] SET TARGET_RECOVERY_TIME = [target recovery time] {SECONDS | MINUTES}

Find more details about Indirect Checkpoint here.

 

3.   Manual Checkpoint

a.   It is issued when user executes T-SQL Command CHECKPOINT.

b.   [Checkpoint duration] parameter is specified in database level.

c.    [Checkpoint duration] parameter specifies requested amount of time in seconds for the checkpoint to complete.

d.   Query to issue Manual checkpoint

                    CHECKPOINT [ checkpoint duration ]

4.   Internal Checkpoint

a.   It is issued internally by database engine during various server operations such as database backup, snapshot creation etc.