Always On FailOver Events

We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened.

cls

 Get-winEvent -ComputerName ListerNameHere -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}|

 Where-Object {$_.Timecreated -like '*05/09/2020*'}|Format-Table -AutoSize -Wrap

<#

35201-->timeout alert run this by changing -LogName to System

41-->41 is power shutdown run this in system

#>

clear

Get-EventLog -LogName Application -After(Get-Date).AddHours(-48)|

Where-Object{$_.EventID -in (1100,1074,35201,41,1561,7024,1135,7032,1146)}| Format-Table -AutoSize -Wrap

cls 

Get-EventLog `
-LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap

EXEC sp_readerrorlog 0,1,'lease worker'

EXEC sp_readerrorlog 0,1,'manual'

EXEC sp_readerrorlog 0,1,'changing roles'

go 

EXEC sp_readerrorlog 0,1,'The lease between availability group'
GO
EXEC sp_readerrorlog 0,1,'connection timeout'
go
EXEC xp_readerrorlog 0,1,"RESOLVING",NULL,"2020-05-10",NULL

GO

EXEC sp_readerrorlog 0,1,"AvailabilityGroup or ListenerNameHere"

GO

EXEC sp_readerrorlog 0,1,"timeout"

GO

EXEC sp_readerrorlog 0,1,"insufficient system memory"

GO

---This shows IO level issues like READ and WRITE operations.This is also important sometimes.

EXEC sp_readerrorlog 0,1,'longer than'

Sometimes these timeout issues might occur due to there is security patches or hotfixes update in the environment. This is also caused to the failover.

And also check in the Windows Event viewer(eventvwr) Source as(Application) "Windows Error Reporting", this  will create a DUMP files, not all the times these dump files will create, but sometimes it creates error log files with appropriate reasons. Especially failover happens between replicas.

clear
Get-EventLog -LogName System |
Where-Object{($_.TimeWritten -like '*11/10/2020*')-and($_.EventID -in (6008,1100,1074,35201,41,1561,7024,1135,7032,1146,1001,1127,1795,1069,1792,1038,1177,17053,1557,14,1573,4199,13,35206,1672,1069,7000,7038,1068,5123,1814,17204))}| Format-Table -AutoSize -Wrap

This information provids your any heartbeat failures.

cls
Get-WinEvent -LogName Microsoft-Windows-FailoverClustering/Operational -MaxEvents 100|
Where-Object {$_.TimeCreated -like "*3/04/2021*"}|Format-Table TimeCreated, Message -AutoSize -wrap

The below commnds will provide the information of cluster resource information.

cls
Get-Cluster|Format-Table -AutoSize
Get-ClusterAvailableDisk|Format-Table -AutoSize
Get-ClusterGroup|Format-Table -AutoSize
Get-ClusterNetwork|Format-Table -AutoSize
Get-ClusterNetworkInterface|Format-Table -AutoSize
Get-ClusterOwnerNode|Format-Table -AutoSize
Get-ClusterResource|Format-Table -AutoSize

CLS
$logs = @('System','Application','Operations Manager')
foreach($log in $logs){
Get-EventLog `
 -LogName $log | 
 Where-Object {($_.TimeWritten -like '*01/07/2023*')-and
 (($_.EventID -in (6008,1100,1074,35201,41,1561,7024,1135,7032,1146,1001,1127,1795,1069,1792,1038,1177,17053,1557,14,1573,4199,13,35206,1672,1069,7000,7038,1068,5123,1814,17204,7034,7001)-OR($_.Message -like '*MSSQLSERVER*')))}|
 Format-Table -Wrap 
}

The below script will run in multipl servers. Here it will run in two servers.

cls
$serverList = 'CopmputerName1','ComputerName2'
foreach($server in $serverList){
$logs = @('System','Application','Operations Manager')
foreach($log in $logs){
Get-EventLog `
 -LogName $log -ComputerName $server| 
 Where-Object {($_.TimeWritten -like '*11/11/2023*')-and
 (($_.EventID -in (6008,1100,1074,35201,41,1561,7024,1135,7032,1146,1001,1127,1795,1069,1792,1038,1177,17053,1557,14,1573,4199,13,35206,1672,1069,7000,7038,1068,5123,1814,17204,7034,7001)-OR($_.Message -like '*MSSQLSERVER*')))}|
 Format-Table @{Name='ComputerName';Expression={($server)}},
 @{Name='EventLog';Expression={($log)}},TimeWritten,EntryType,Source, Message -Wrap 
}
}

We can also use below query, if the above query does not work sometimes.

cls
$servernames= @('ComputerName1','Co puterName2')
foreach($server in $servernames){
Invoke-Command `
-ComputerName $server -ScriptBlock {
$logs = @('System','Application')
foreach($log in $logs){
Get-EventLog `
 -LogName $log | 
 Where-Object {($_.TimeWritten -like '*11/12/2023*')-and
 (($_.EventID -in (6008,1100,1074,35201,41,1561,7024,1135,7032,1146,1001,1127,1795,1069,1792,1038,1177,17053,1557,14, `
 1573,4199,13,35206,1672,1069,7000,7038,1068,5123,1814,17204,7034,7001) `
 -OR($_.Message -like '*MSSQLSERVER*')))}| 
 Select-Object MachineName,TimeWritten,@{Name='EventType';Expression={($log)}},Message|Format-Table -Wrap}}}