Manually run the Checkpoint in MS SQL Server

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:

1. 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:

checkpoint

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