SQL Server Always On Series - TSQL Query to get Availability group failover time from SQL Error log
SQL Server Always On Series - TSQL Query to get Always On Availability group failover time from SQL Server Error log @jbswiki #alwayson #sqlserver #highavailability
We will test a TSQL Query to get Always On Availability group failover time from SQL Server Error log.
2023-03-18 16:02:25.290 spid79 The state of the local availability replica in availability group 'JBSAG' has changed from 'PRIMARY_NORMAL' to 'RESOLVING_NORMAL'. The state changed because the availability group is going offline. The replica is going offline because the associated availability group has been deleted, or the user has taken the associated availability group offline in Windows Server Failover Clustering (WSFC) management console, or the availability group is failing over to another SQL Server instance. For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2023-03-18 16:02:29.730 spid38s The state of the local availability replica in availability group 'JBSAG' has changed from 'RESOLVING_NORMAL' to 'SECONDARY_NORMAL'. The state changed because the availability group state has changed in Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2023-03-18 16:02:25.290 spid80 The state of the local availability replica in availability group 'JBSAG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'. The state changed because of a user initiated failover. For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2023-03-18 16:02:29.130 spid91 The state of the local availability replica in availability group 'JBSAG' has changed from 'RESOLVING_PENDING_FAILOVER' to 'RESOLVING_NORMAL'. The state changed because the availability group is coming online. For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2023-03-18 16:02:29.610 spid91 The state of the local availability replica in availability group 'JBSAG' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'. The state changed because the availability group is coming online. For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2023-03-18 16:02:30.380 Server The state of the local availability replica in availability group 'JBSAG' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'. The state changed because the local replica has completed processing Online command from Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log or cluster log. If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
Reference : https://jbswiki.com/2020/01/07/script...
