This article guides on
Database recovery is slow due to log file issue in SQL Server Always ON failover? | How to troubleshoot database startup slowness in SQL Server Always ON failover ?
What is DB startup in SQL Server?
Whenever there is planned or unplanned SQL Server stop or restart , SQL server will recover the databases using DB startup process. This process is internal and managed by SQL Server automatically. Once SQL Service up it start recovery of all the databases. Time required for those databases recovery is depend on DB size, Server configuration, transaction need to be recovered,, log file size etc.
How DB Startup work in SQL Server Always ON?
Always ON in SQL Server is one of the best high availability technique. In DB startup process with Always ON there is not much difference but as AG having multiple replica associated depending on AG mode database recovery start and get complete.
Why DB recovery slow down in Always on failover process when log file is huge?
Database log file is the place where all transaction get logged and this help to recover database when there is disaster. This process is called point in time restore. Always ON also use log file to sync other replica participated.
If log file grow very large due to some transactions or other system event it has to sync with other replica as well. sync process depend on various factor
If there is SQL service storage or restart , AG sync start by using log file and move all those pending redo log data to other node. So log file size is huge and also number of VLF count is large DB startup or sync process takes time.
Best Practices to keep log file maintained in SQL Server Always ON.
Step 1 : Check database log file size using DBCC SQLPERF(LOGSPACE)
Step 2 : Check number of VLF in log file using DBCC LOGINFO
Step 3 : If VLF count is very large this create slowness in database recovery. So reduce the number VLF count as much as you can.
Step 4 : Check database log file growth setting. If auto growth is configured not optimal as per number of transaction getting processed change it to higher value and monitor the performance.
Step 5 : Make sure transaction log backup configured and backup an taking place.
By following theses steps you can easily maintain log file size and growth. Once you have optimal log file growth Always ON failover process time will get reduced automatically and able to resolve Database recovery is slow due to log file issue in SQL Server Always ON failover.