Would like to optimize recovery time for db's after failover to passive node in high availability cluster.
Assuming full recovery model…
What are the steps to take to optimize DB recovery time for a given SQL server database?
Is there a good way to estimate how long recovery time will take for a given SQL server database?
Best Answer
The best way is to upgrade to SQL Server 2019 and use Accelerated Database Recovery which provides:
Fast and consistent database recovery
Instantaneous transaction rollback
Aggressive log truncation
On older versions you can configure the recovery interval to force more aggressive checkpointing of dirty pages, but in-flight transactions will still need to be rolled back during a failover. So you would also want to avoid long-running transactions during windows where you need fast failover.
Recovery time during large transactions might be optimized by temporarily switching the database to the Bulk Logged recovery model, but you'd need to ensure that the operations qualify for minimal logging.