Recovery time is determined by how much work has been done since the last checkpoint, and by how much work has been done by all active transactions at the time of the server crash. Microsoft® SQL Server™ uses a configuration option named recovery interval to set the maximum number of minutes per database that SQL Server needs to recover databases. This recovery interval setting controls checkpoint frequency. For an online transaction processing (OLTP) system (using short transactions), recovery interval is the primary factor determining recovery time.
After installation, SQL Server sets this recovery interval setting to a default value of zero (0). As long as the recovery interval setting is at the default setting and long-running transactions are not present, recovery for each database should take approximately 1 minute or less. If long-running transactions were active at the time of the server crash, recovery time is controlled by the time it takes to rollback the effects of these transactions.
If recovery routinely takes significantly longer than 1 minute for a database, the recovery interval setting has a value of zero (0), and there are no long-running transactions to rollback, consider contacting your primary support provider to resolve the recovery performance problem.
Recovery reports progress (based on the virtual log files for a database). Recovery analyzes and scans the log at the beginning of recovery, since the last checkpoint. Based on the analysis phase, recovery estimates how much log will be read during recovery. The amount of log read is used to report recovery progress.
If the recovery interval setting is changed from the default value, database recovery takes that many times longer to complete. For example, if recovery interval is changed to 10, recovery would take approximately 10 times longer to complete than if recovery interval remained at the default setting of zero (0).
When growing the log, use larger chunks rather than small chunks to ensure a shorter startup time for SQL Server. The more small log chunks you have, the longer it takes SQL Server to initialize them.
If a long-running transaction is terminated, let the server finish the rollback process. If you are concerned about the length of the rollback process, ask your system administrator to confirm that activity is taking place on the server. Terminating the server process during the rollback of a long-running transaction results in long recovery time.
If you have a long-running transaction and a crash occurs during this transaction, SQL Server begins the recovery process. This may take some time. If you are concerned that this recovery process is taking too long and you believe it is halted, contact your system administrator.
| recovery interval Option | sp_configure |
| Setting Configuration Options |