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

This topic describes how to set the change the target recovery time of a SQL Server database in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. By default, the target recovery time is 0, and the database uses automatic checkpoints (which are controlled by the recovery interval server option). Setting the target recovery time to greater than 0 causes the database to use the indirect-checkpoints and establishes an upper-bound on recovery time for this database.

Note

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, Security

• To change the target recovery time, using:  SQL Server Management Studio or Transact-SQL

Before You Begin

Limitations and Restrictions

• An online transactional workload on a database that is configured for indirect checkpoints could experience performance degradation.

Security

Permissions

Requires ALTER permission on the database.

[Top]

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. 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 on the recovery time for this database.

[Top]

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 }

   • target_recovery_time
     When greater than 0 (the default), specifies the upper-bound on the recovery time for the specified database in the event of a crash.

   • SECONDS
     Indicates that target_recovery_time is expressed as the number of seconds.

   • MINUTES
     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 90 seconds.

   ALTER DATABASE AdventureWorks2012 SET TARGET_RECOVERY_TIME = 90 SECONDS;
   

[Top]

See Also

Reference

ALTER DATABASE SET Options (Transact-SQL)

Concepts

Database Checkpoints (SQL Server)