What is Checkpoint. When it occurs.

Checkpoint is like a save button for SQL Server. Whatever the un-saved data (uncommitted data to say technically) in the database gets saved (in technical terms, will be written to disk i.e.., to data file) whenever checkpoint occurs. Checkpoint occurs automatically in SQL Server.

When will checkpoint occur in a database:

                                                                                                                                   

·         Recovery interval option at the server level is used by SQL Server to calculate when checkpoints to be issued. This is the time in which SQL server should recover all the databases of the instance after a restart. So we can think of it as a threshold SQL server keeps monitoring, the recovery of the database depends on the number of log records in it, so SQL server keeps calculating with the current number of log records in the database how much time it takes to recover it and when it finds the recovery time becomes equal to setting ‘recovery interval’ option then SQL Server issues a CHECKPOINT.

·         If any DB files are added or removed by using ALTER DATABASE command.

·         If the instance has been stopped. Either by SHUTDOWN command or by stopping the SQL service.

·         During backup and database snapshot creation.

·         A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.

·         Also depending on number of data modifications going on in the DB, SQL Server automatically issues CHECKPOINT on the DB. If there are many number of data modifications going on then there would be frequent CHECKPOINTs and vice versa.

Thanks!!