Skip to content

checkpoint-transact-sql.md

Latest commit

 

History

History
85 lines (61 loc) · 4.62 KB

checkpoint-transact-sql.md

File metadata and controls

85 lines (61 loc) · 4.62 KB
title CHECKPOINT (Transact-SQL)
description CHECKPOINT generates a manual checkpoint in the current database.
author rwestMSFT
ms.author randolphwest
ms.date 08/20/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
CHECKPOINT_TSQL
CHECKPOINT
helpviewer_keywords
events [SQL Server], checkpoints
automatic checkpoints
writing dirty pages to disk
pages [SQL Server], dirty
checkpoints [SQL Server]
CHECKPOINT statement
log truncate mode [SQL Server]
dirty pages
logs [SQL Server], checkpoints
manual checkpoints [SQL Server]
pages [SQL Server], checkpoints
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

CHECKPOINT (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Fabricsqldb]

Generates a manual checkpoint in the [!INCLUDEssNoVersion] database to which you are currently connected.

Tip

For information about different types of database checkpoints and checkpoint operation in general, see Database checkpoints (SQL Server).

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

CHECKPOINT [ checkpoint_duration ]

Arguments

checkpoint_duration

Specifies the requested amount of time, in seconds, for the manual checkpoint to complete.

checkpoint_duration is an advanced option.

When checkpoint_duration is specified, the [!INCLUDEssDEnoversion] attempts to perform the checkpoint within the requested duration.

The checkpoint_duration must be an expression of type int and must be greater than zero.

When this parameter is omitted, the [!INCLUDEssDE] adjusts the checkpoint duration to minimize the performance impact on database applications.

Factors affecting the duration of checkpoint operations

In general, the amount time required for a checkpoint operation increases with the number of dirty pages that the operation must write. By default, to minimize the performance impact on other applications, [!INCLUDEssNoVersion] adjusts the frequency of writes that a checkpoint operation performs. Decreasing the write frequency increases the time the checkpoint operation requires to complete. [!INCLUDEssNoVersion] uses this strategy for a manual checkpoint unless a checkpoint_duration value is specified in the CHECKPOINT command.

The performance impact of using checkpoint_duration depends on the number of dirty pages, the activity on the system, and the actual duration specified.

  • For example, if the checkpoint would normally complete in 120 seconds, specifying a checkpoint_duration of 45 seconds causes [!INCLUDEssNoVersion] to devote more resources to the checkpoint than would be assigned by default.

  • In contrast, specifying a checkpoint_duration of 180 seconds would cause [!INCLUDEssNoVersion] to assign fewer resources than would be assigned by default.

In general, a short checkpoint_duration will increase the resources devoted to the checkpoint, while a long checkpoint_duration will reduce the resources devoted to the checkpoint. [!INCLUDEssNoVersion] always completes a checkpoint if possible, and the CHECKPOINT statement returns immediately when a checkpoint completes. Therefore, in some cases, a checkpoint can complete sooner than the specified duration or might run longer than the specified duration.

Permissions

CHECKPOINT permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles, and are not transferable.

Related content