Change the Target Recovery Time of a Database (SQL Server)

Applies to: yesSQL Server (all supported versions)

This topic describes how to set or change the target recovery time of a database in SQL Server by using SQL Server Management Studio or Transact-SQL. By default, the target recovery time is 60 seconds, and the database uses indirect checkpoints. The target recovery time establishes an upper-bound on recovery time for this database.


The upper-bound that is specified for a given database by its target recovery time setting could be exceeded if a long-running transaction causes excessive UNDO times.

Before You Begin

Limitations and Restrictions


An online transactional workload on a database that is configured for indirect checkpoints could experience performance degradation. Indirect checkpoints make sure that the number of dirty pages are below a certain threshold so that the database recovery completes within the target recovery time. The recovery interval configuration option uses the number of transactions to determine the recovery time as opposed to indirect checkpoints which makes use of the number of dirty pages. When indirect checkpoints are enabled on a database receiving a large number of DML operations, the background writer can start aggressively flushing dirty buffers to disk to ensure that the time required to perform recovery is within the target recovery time set on the database. This can cause additional I/O activity on certain systems, which can contribute to a performance bottleneck if the disk subsystem is operating above or nearing the I/O threshold.



Requires ALTER permission on the database.

Using SQL Server Management Studio

To change the target recovery time

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and expand that instance.

  2. Expand the Databases container, then right-click the database you want to change, and click the Properties command.

  3. In the Database Properties dialog box, click the Options page.

  4. In the Recovery panel, in the Target Recovery Time (Seconds) field, specify the number of seconds that you want as the upper-bound of the recovery time for this database.

Using Transact-SQL

To change the target recovery time

  1. Connect to the instance of SQL Server where the database resides.

  2. Use the following ALTER DATABASE statement, as follows:

    TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

    Beginning with SQL Server 2016 (13.x), the default value is 1 minute. When greater than 0 (the default for older versions), specifies the upper-bound on the recovery time for the specified database in the event of a crash.

    Indicates that target_recovery_time is expressed as the number of seconds.

    Indicates that target_recovery_time is expressed as the number of minutes.

    The following example sets the target recovery time of the AdventureWorks2012 database to 60 seconds.


See Also

Database Checkpoints (SQL Server)