- Checkpoint is an internal process that writes all the dirty pages (from the buffer cache to the physical disk) to the physical disk,apart from this log also writes log records from the buffer to the physical file.
Writing Dirty Pages in the data file from buffer cache is also known as the hardening of dirty pages. - This is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process individually for each database.
- Checkpoint helps reduce the recovery time for SQL Server in case of an unexpected shutdown or system crash/ Failure.
- This minimizes the active portion of the log that must be processed during a full recovery of a database.
- The log records of modifications not flushed to disk before the system stopped are rolled forward.
- All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.
- Prior to database backup, the database engine automatically performs a checkpoint so that all changes in the database page are contained in the backup.In addition, the server stops issuing a checkpoint in each database on the server.
There are four types of Checkpoints available in SQL Server:-
- Automatic
- Indirect
- Manual
- Internal
- Automatic :-
This is the most common checkpoint, which runs as a process in the background to ensure that the SQL Server database recovery can be retrieved in the time frame defined by the recovery interval – server configuration options.
- Indirect :-
This is new in SQL Server 2012. It also runs in the background, but to meet the user-specified target recovery time for specific databases where the option is configured. Once the target_recovery_Time is selected for the given database, it will override the specified recovery interval for the server and avoid the automatic checkpoint on such a DB.
- Manual :-
it runs just like any other T-SQL statement, once you issue the checkpoint command it will run until it completes. Manual checkpoint only runs for your current database
- Internal :-
A user you can’t control Internal Checkpoint Issued on specific operations like Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean.