recovery interval in minutes

recovery interval in minutes sets the maximum number of minutes per database that SAP ASE uses to complete its recovery procedures in case of a system failure.

Summary Information
Default value	5
Range of values	1–32767
Status	Dynamic
Display level	Basic
Required role	System administrator
Configuration group	Backup/Recovery

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.

SAP ASE 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.

Note: The recovery interval has no effect on long-running, minimally logged transactions (such as create index) that are active when SAP ASE 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.

SAP ASE uses the recovery interval in minutes setting and the amount of activity on each database to decide when to checkpoint each database. When SAP ASE 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. The figure below shows the logic used by SAP ASE during this process.

The checkpoint process
Flow chart showing how the checkpoint process works. See the accompanying text for full explanation.

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 SAP ASE. 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. For more information on the performance implications of recovery interval in minutes, see Performance and Tuning Series: Basics > Memory Use and Performance.

Use sp_sysmon to determine how a particular recovery interval affects the system. See the Performance and Tuning Series: Monitoring SAP Adaptive Server with sp_sysmon.