Q:

What is Checkpoint in SQL Server Transaction, what are the different types of Checkpoint

What is Checkpoint in SQL Server Transaction, what are the different types of Checkpoint

A:

A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log
Automatic
Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints run to completion. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.  
Indirect
Issued in the background to meet a user-specified target recovery time for a given database. The default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance. 
Manual
Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.
Internal
Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.

A:

A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.
While doing batch delete operation forcing 'Checkpoint' helped to deletion faster..

