Summary information |
|
|---|---|
Default value |
5 |
Range of values |
1–32767 |
Status |
Dynamic |
Display level |
Basic |
Required role |
System administrator |
Configuration group |
Backup/Recovery |
recovery interval in minutes 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 when 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 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 5-4 shows the logic used by Adaptive Server during this process.
Figure 5-4: 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. However, setting the recovery interval too high may cause the recovery time to be unacceptably long. You can reduce the spikes caused by checkpointing by reconfiguring the housekeeper freewrite percent parameter. See “housekeeper free write percent”. For more information on the performance implications of recovery interval in minutes, see Chapter 5, “Memory Use and Performance,” in the Performance and Tuning Series: Basics.
Use sp_sysmon to determine how a particular recovery interval affects the system. See the Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.
