For SQL Server Database Administrators, tracking the timing of cluster failovers is crucial for maintaining database availability and performance. PowerShell provides a quick way to retrieve the last SQL Server failover time by reading Event Logs, which we will share in this article.

One critical aspect of Windows Failover Cluster Management is understanding when and why clustered roles move from one node to another within a cluster. In Windows environments, this information is logged with a specific event ID (1641 in this case) to the local system. The Get-WinEvent PowerShell cmdlet allows administrators to retrieve and filter these events effectively. Below, we share the command that will retrieve the clustered role movements.

PowerShell Command to Get Last SQL Failover Events

Amend ListenerName in the PowerShell script line below to your Always On Availability Group Listener Name before running:

# Get last failover time
Get-WinEvent -ComputerName ListenerName -FilterHashtable @{logname='Microsoft-Windows-FailoverClustering/Operational'; id=1641} | Format-Table -AutoSize -Wrap

Hope this script helps!

Understanding Failover Events in SQL Server

In SQL Server environments configured with high availability solutions such as Always On Availability Groups or failover clustering, failover events occur when the active node transitions to a standby node due to various reasons, including hardware failures, maintenance activities, or load balancing. Monitoring these failover events and understanding their timing is essential for ensuring continuous service availability and diagnosing potential issues promptly.

Moreover, distinguishing between automatic and manual failovers is crucial. Automatic failovers occur automatically when the active node becomes unavailable, ensuring minimal downtime and uninterrupted service. In contrast, manual failovers are initiated by administrators for planned maintenance or troubleshooting, requiring careful consideration to minimize disruptions.

For more information on Windows Failover Cluster System Log Events, as always check out the Microsoft Docs. This page linked shares information on all levels of FailoverClustering events, including Critical, Error, Warning, and Informational.