March 28, 2017

Activities That Cause a Checkpoint in SQL Server

Checkpoints occur in the following situations:

1. A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.

2. 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.

3. Database files have been added or removed by using ALTER DATABASE.

4. An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Either action causes a checkpoint in each database in the instance of SQL Server.

5. An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.

6. A database backup is taken.

7. An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

8. if we have large size dB and rebuild, re-index jobs in this Database are stopped in between it trigger checkpoint processes .