recovery interval Option

Use the recovery interval option to set the maximum number of minutes per database that Microsoft® SQL Server™ needs to recover databases. Each time an instance of SQL Server starts, it recovers each database, rolling back transactions that did not commit and rolling forward transactions that did commit but whose changes were not yet written to disk when an instance of SQL Server stopped. This configuration option sets an upper limit on the time it should take to recover each database. The default is 0, indicating automatic configuration by SQL Server. In practice, this means a recovery time of less than one minute and a checkpoint approximately every one minute for active databases.

recovery interval controls when SQL Server issues a checkpoint in each database. Checkpoints are done on a per database basis. At a checkpoint, SQL Server ensures all log information and all modified pages are flushed from memory to disk. This limits the time needed for recovery by limiting the number of transactions rolled forward to ensure they are on disk. No modifications done before the checkpoint need to be rolled forward because they have been flushed to disk at the checkpoint.

recovery interval does not affect the time it takes to undo long-running transactions. For example, if a long-running transaction has taken two hours to perform updates before the server became disabled, the actual recovery will take considerably longer than the recovery interval value to roll back the long transaction.

SQL Server estimates how many data modifications it can roll forward in the recovery time interval. SQL Server typically issues a checkpoint in a database when the number of data modifications made in the database after the last checkpoint reaches the number SQL Server estimates it can roll forward in the recovery time interval. Sometimes SQL Server will issue the checkpoint when the log becomes 70 percent full, if that is less than the estimated number. For more information, see Checkpoints and the Active Portion of the Log.

The frequency of checkpoints in each database depends on the amount of data modifications made, not on any time-based measure. A database used primarily for read-only operations will not have many checkpoints. A transaction database will have frequent checkpoints.

Keep recovery interval set at 0 (self-configuring) unless you notice that checkpoints are impairing performance because they are occurring too frequently. If this is the case, try increasing the value in small increments.

recovery interval is an advanced option. If you will be using the sp_configure system stored procedure to change the setting, you can change recovery interval only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).

To set the recovery interval