SQL CHECKPOINT

What does the SQL CHECKPOINT statement do?

Forces a database checkpoint, writing all dirty pages and transaction log information to disk so that the database can recover quickly after a crash.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CHECKPOINT: SQL Server, PostgreSQL, SAP ASE, IBM Db2 LUW. Not supported as a statement in MySQL, MariaDB, or SQLite.

SQL CHECKPOINT Full Explanation

CHECKPOINT is a database-level command that flushes all modified (dirty) data pages from memory to disk and records a log marker indicating that the data files are consistent up to that point. During crash recovery, the engine only needs to apply log records generated after the most recent checkpoint, significantly shortening restart time. Issuing CHECKPOINT does not commit or roll back any open transaction; it simply guarantees that everything already written to the transaction log is now durable in the data files. DBAs typically rely on the automatic checkpointing mechanism built into the engine, but manual checkpoints are useful before large bulk operations, after massive data changes, or prior to taking a file-system level backup. Because the operation is I/O intensive, frequent manual checkpoints can hurt performance if timed poorly. In SQL Server, you can optionally specify a target duration in seconds. PostgreSQL provides no parameters but requires superuser privileges. Other engines use different checkpointing mechanisms or vocabulary.

SQL CHECKPOINT Syntax

-- SQL Server
a) Default
CHECKPOINT;

b) Timed checkpoint
CHECKPOINT <checkpoint_duration_seconds>;

-- PostgreSQL
CHECKPOINT;

SQL CHECKPOINT Parameters

Example Queries Using SQL CHECKPOINT

-- Flush dirty pages immediately (SQL Server or PostgreSQL)
CHECKPOINT;

-- Ask SQL Server to finish within 45 seconds
CHECKPOINT 45;

-- Wrap in a maintenance script
BEGIN TRAN;
UPDATE Sales.Orders SET status = 'archived' WHERE order_date < '2021-01-01';
COMMIT;
CHECKPOINT;

Expected Output Using SQL CHECKPOINT

The database engine starts a checkpoint job that writes all dirty pages to disk, truncates the active portion of the transaction log if possible, and records a checkpoint LSN
The client receives a success message; no result set is returned

Use Cases with SQL CHECKPOINT

Shorten crash-recovery time before planned maintenance.
Ensure file-system or VM-level snapshots capture a consistent on-disk image.
Free up space in the transaction log after large data loads in Simple/Bulk-Logged recovery models (SQL Server).
Force a consistent state prior to detaching, cloning, or copying a database file.

Common Mistakes with SQL CHECKPOINT

Assuming CHECKPOINT commits open transactions – it does not.
Confusing CHECKPOINT with SAVEPOINT, which marks a transaction position for partial rollback.
Running checkpoints too frequently, causing unnecessary disk I/O.
Forgetting that non-superusers cannot execute CHECKPOINT in PostgreSQL.

First Introduced In

Sybase SQL Server 4.x (early 1990s); later adopted by Microsoft SQL Server and PostgreSQL 7.1

Frequently Asked Questions

Does CHECKPOINT commit my open transaction?

No. CHECKPOINT only flushes dirty pages and records a log marker. Your transaction remains active until you explicitly COMMIT or ROLLBACK.

Is manual CHECKPOINT necessary in SQL Server?

In most workloads the automatic checkpointing process is sufficient. Manual checkpoints are helpful before maintenance tasks, bulk operations, or file-system snapshots when you need a guaranteed consistent on-disk state.

Can any user run CHECKPOINT in PostgreSQL?

No. Only superusers or members of the pg_checkpoint role (from PostgreSQL 14 onward) can execute CHECKPOINT because it may cause heavy I/O across the entire cluster.

How can I make a checkpoint run faster?

In SQL Server specify a checkpoint duration (e.g., CHECKPOINT 30) to let the engine throttle writes over a longer period. On PostgreSQL you cannot throttle a manual checkpoint, but you can adjust configuration parameters like checkpoint_completion_target and max_wal_size to influence automatic checkpoints.