What are the Different Types of SQL Server CHECKPOINT? – Interview Question of the Week #230

Question: What are SQL Server’s checkpoint types? Automatic, indirect, manual and internal checkpoints.

A checkpoint writes modified data pages and recovery information, so the engine has a known starting point for crash recovery. It doesn’t commit an open transaction, remove all cached pages or replace a backup.

These targets concern recovery time, not a timer that guarantees a checkpoint every specified number of seconds. New databases from SQL Server 2016 normally use a 60-second indirect target. A restored or upgraded database can keep its earlier setting, so inspect it.

-- Read-only inspection.
SELECT name, target_recovery_time_in_seconds
FROM sys.databases
WHERE database_id = DB_ID();
SELECT name, value_in_use
FROM sys.configurations
WHERE name = N'recovery interval (min)';

-- Syntax examples only. Do not run these to answer the interview question.
-- A reviewed server-level change also requires enabling advanced options
-- if necessary and applying RECONFIGURE.
-- EXEC sys.sp_configure N'recovery interval (min)', 1;
-- RECONFIGURE;
-- ALTER DATABASE CURRENT SET TARGET_RECOVERY_TIME = 60 SECONDS;
-- CHECKPOINT;

The first two queries only read the current settings. The commented statements show the configuration and manual syntax without silently changing your instance. A server configuration change needs the appropriate permission, advanced-option handling and RECONFIGURE; it’s a separate administrative decision.

Writing pages costs I/O. A more aggressive recovery target isn’t automatically a performance improvement. Understand storage capacity and recovery requirements before changing it.

A checkpoint is not a commit, it is a bookmark for recovery, and four different triggers can write it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.