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
To change the target recovery time
-
In Object Explorer, connect to an instance of the SQL Server Database Engine, and expand that instance.
-
Right-click the database you want to change, and click the Properties command.
-
In the Database Properties dialog box, click the Options page.
-
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.
To change the target recovery time
-
Connect to the instance of SQL Server where the database resides.
-
Use the following ALTER DATABASE statement, as follows:
TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | 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;
