Checkpoint

Why Checkpoints?
❖ To reduce crash recovery time. When server was restarted unexpectedly if many dirty pages are in buffer then recovery process takes long time to make database consistent. 
❖ To batch I/Os to disk to get better performance.

Checkpoint occurs in the following scenarios :
1. Periodically (Depends on recovery interval of SS) R.C on Instance--> Properties-->Database settings--> Recovery interval =1 
2. When the database backup is about to start 
3. If the server is started. 
4. If the T.Log is 70% full and it is in Log truncate mode. 
5. When a new data or T.Log file was added. 
6. Manually using CHECKPOINT command. 
7. When database is detached. 
8. When we change recovery model from FULL or BULK_LOGGED to SIMPLE. 
9. When database snapshot is generated. 
10.When Bulk operation was done in bulk logged recovery model. 

Checkpoint Types

Automatic : It depends on recovery interval time.

Manual : We can force checkpoint manually by using command CHECKPOINT.

Indirect : These are introduced in SQL Server 2012.

           Takes more priority than automatic checkpoints.

           USE master 
           GO 

           Alter database TestDB set target_recovery_time = 5 seconds with no_wait

Internal.

Note: To verify when the checkpoint has raised -we can use 
                 DBCC LOG('DbName',3) AS DBCC LOG('Sales',3)

To verify latest checkpoint details :

SELECT TOP 1 f1.[Checkpoint Begin], f2.[Checkpoint End] 

FROM fn_dblog(NULL, NULL) f1 

INNER JOIN fn_dblog(NULL, NULL) f2 ON f1.[Current LSN] = f2.[Previous LSN] 

WHERE f2.Operation IN (N'LOP_BEGIN_CKPT', N'LOP_END_CKPT') ORDER BY 1 DESC

To find page details :

SELECT Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty' ELSE 'Clean' END, DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(database_id) END, Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY database_id, is_modified
ORDER BY 2

--checkpoint