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.
The recovery interval option controls when SQL Server issues a checkpoint in each database. Checkpoints are done on a per-database basis. At a checkpoint, SQL Server ensures that 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. Modifications before the checkpoint do not need to be rolled forward because they were already flushed to disk at the checkpoint.
The recovery interval option does not affect the time it takes to undo long-running transactions. For example, if a long-running transaction takes two hours to perform updates before the server becomes disabled, the actual recovery takes 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.
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 impair performance because they occur too frequently. If this is the case, try increasing the value in small increments.
The recovery interval option is an advanced option. If you are 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 restart.
To set the recovery interval option to a value greater than 60 (minutes) requires that you run the RECONFIGURE WITH OVERRIDE statement.
See Also
Concepts
Setting Server Configuration Options
Other Resources
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)
sp_dboption (Transact-SQL)