Summary information |
|
|---|---|
Default value |
5 |
Range of values |
1–32767 |
Status |
Dynamic |
Display level |
Basic |
Required role |
System Administrator |
The recovery interval in minutes parameter sets the maximum number of minutes per database that Adaptive Server uses to complete its recovery procedures in case of a system failure. The recovery procedure rolls transactions backward or forward, starting from the transaction that the checkpoint process indicates as the oldest active transaction. The recovery process has more or less work to do depending on the value of recovery interval in minutes.
Adaptive Server estimates that 6000 rows in the transaction log require 1 minute of recovery time. However, different types of log records can take more or less time to recover. If you set recovery interval in minutes to 3, the checkpoint process writes changed pages to disk only when syslogs contains more than 18,000 rows since the last checkpoint.
The
recovery interval has no effect on long-running, minimally logged transactions
(such as create index) that are active at the
time Adaptive Server fails. It may take as much time to reverse
these transactions as it took to run them. To avoid lengthy delays,
dump each database after index maintenance operations.
Adaptive Server uses the recovery interval in minutes setting and the amount of activity on each database to decide when to checkpoint each database. When Adaptive Server checkpoints a database, it writes all dirty pages (data pages in cache that have been modified) to disk. This may create a brief period of high I/O, called a checkpoint spike.The checkpoint also performs a few other maintenance tasks, including truncating the transaction log for each database for which the truncate log on chkpt option has been set. About once per minute, the sleeping checkpoint process “wakes up,” checks the truncate log on chkpt setting, and checks the recovery interval to determine if a checkpoint is needed. Figure 4-1 shows the logic used by Adaptive Server during this process.
Figure 4-1: The checkpoint process
You may want to change the recovery interval if your application and its use change. For example, you may want to shorten the recovery interval when there is an increase in update activity on Adaptive Server. Shortening the recovery interval causes more frequent checkpoints, with smaller, more frequent checkpoint spikes, and slows the system slightly. On the other hand, setting the recovery interval too high might cause the recovery time to be unacceptably long. The spikes caused by checkpointing can be reduced by reconfiguring the housekeeper free write percent parameter. See “housekeeper free write percent” for further information. For more information on the performance implications of recovery interval in minutes, see “Memory Use and Performance” in the Performance and Tuning: Basics.
Use sp_sysmon to determine how a particular recovery interval affects the system. See the Performance and Tuning Guide for more information.
