We had a strange issue post our regular OS patching for SQL servers.
Per best practices, we apply patch on Passive and do node failovers to make current passive, active and vice versa to complete patching.
Generally node failover is seamless and completes under a min. However recently we had issue where it took 4 minutes to bring SQL online after the node failover:
I am checking logs and events but could not find the reason: below is the findings so far:
Note: The SQL server is running on VM
- No increased in SQL server activity
- Databases are part of DB mirroring
- No increase in user connections or user queries running longer in that duration
- VLF under 500 for all databases
- NO CPU/Memory pressure and LPIM is enabled.
- The process which was seemed to killed time out long running during failover was EXEC sp_server_diagnostics 20 running for past 86745234 secs
Please assist what else should i be checking to find the root cause?
Edit- I tried analyzing the cluster log and can see sql offline was initiated but i am not sure where it spend atleast 4 mins internally to actually shut down sql and bring it back. after 4 mins sql error log had all enteries for databases being brought up approx 10 secs. So it looks DB might not hve any involvement here for slowing down process.
Edit- some VLF info when checked currently