Understanding Recovery Performance in SQL Server
Recovery performance focuses primarily on crash recovery rather than on recovery after restoring a backup. However, optimizations are possible for recovery after restoring from a backup.
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 data loss. SQL Server uses a configuration option called recovery interval to set the approximate 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 recovery interval to zero. 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. When recovering restored data, if long-running transactions were active at the time of the data loss, recovery time is controlled by the time it takes to roll back the effects of these transactions. However, in SQL Server 2005, the database is available during the undo phase of a crash recovery or a database mirroring failover, a feature known as fast recovery.
If recovery routinely takes significantly longer than 1 minute for a database, the recovery interval setting has a value of zero, and there are no long-running transactions to roll back, 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.
When growing the log, use larger increments rather than smaller increments to ensure a shorter startup time for SQL Server. The smaller the log increments you have, the longer it takes SQL Server to initialize them.
When recovering after a restore operation, let the server finish the rollback process if a long-running transaction was terminated. Terminating the server process during the rollback of a long-running transaction results in long recovery time. 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.
If you have a long-running transaction and a crash occurs during this transaction, SQL Server begins the recovery process. In this case, recovery speed is increased because the database is available during the undo phase.
For ways of reducing recovery time when restoring data from backups under the full recovery model, see Reducing Recovery Time When Restoring a Database.
See Also
Tasks
How to: Set the Recovery Interval (SQL Server Management Studio)
Concepts
recovery interval Option
Setting Server Configuration Options