Checkpoint in Microsoft SQL Server
When a checkpoint occurs database flushes the dirty pages to disk. It writes the transaction log from memory to the disk for permanent recording information in the transaction log.
SQL Server support 4 types of the checkpoint:
- Automatic Checkpoints: It’s occurred at specified time intervals with recovery interval server configuration. The default value for the Recovery interval is 0 which means 60 seconds. You can change the “recovery interval” to 3 min(180 seconds)
--Check the value SELECT value FROM sys.configurations where name = 'recovery interval (min)' Value ---- 0 --- Configure the Recovery interval to 3 min USE [MASTER] GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE GO EXEC sp_configure 'recovery interval', 3 GO RECONFIGURE GO SELECT value FROM sys.configurations where name = 'recovery interval (min)' Value ----- 3
2. Manual Checkpoint: Do the Manual checkpoint in SQL Server with the following command:
3. Indirect Checkpoint: From SQL Server 2012, it configured at database level alternative to automatic checkpoint:
ALTER DATABASE DBNAME SET TARGET_RECOVERY_TIME=time in Seconds/Minutes Example: ALTER DATABASE AdventureWorks2019 SET TARGET_RECOVERY_TIME = 60 SECONDS; ALTER DATABASE AdventureWorks2019 SET TARGET_RECOVERY_TIME = 60 MINUTES;
4. Internal Checkpoints: It’s run with the event like ALTER DATABASE command by server components to make your changes written to disk.
Following are the event cause internal Checkpoints: Adding removing database files Backup of the database DBCC CHECK create database snapshot. shutdown stop the MSSQLSERVER Service Failover