Find the history of AlwaysOn failovers
Read the failover cluster event log, the current primary replica and the SQL Server error log to rebuild the failover history of an availability group.
The history of AlwaysOn failovers can be rebuilt from three sources: the Windows failover cluster event log, the current primary replica of each availability group, and the SQL Server error log.
Failover cluster events
Event ID 1641 in the Microsoft-Windows-FailoverClustering/Operational log is read with Get-WinEvent:
Get-WinEvent -ComputerName sqlserver01 -FilterHashtable @{LogName='Microsoft-Windows-FailoverClustering/Operational'; Id=1641} | Format-Table -AutoSize -Wrap
Current primary replica
The query below lists each availability group with the primary replica and the role of the local replica.
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC
History from the error log
The script below reads all the SQL Server error log files and returns the lines that contain NETBIOS or SQL Server is terminating.
CREATE TABLE #ErrorLog(
LogDate DATETIME,
ErrorSource NVARCHAR(MAX),
ErrorMessage NVARCHAR(MAX)
)
CREATE TABLE #NumberOfLogs(
ID INT PRIMARY KEY NOT NULL,
LogDate DATETIME NOT NULL,
LogFileSize bigint
)
INSERT INTO #NumberOfLogs(ID,LogDate,LogFileSize)
EXEC master.dbo.xp_enumerrorlogs
DECLARE @ErrorLogID INT
DECLARE cNumberOfLogs CURSOR FOR
SELECT ID
FROM #NumberOfLogs
OPEN cNumberOfLogs
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
EXEC sp_readerrorlog @ErrorLogID, 1, 'NETBIOS'
INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
EXEC sp_readerrorlog @ErrorLogID, 1, 'SQL Server is terminating'
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
END
CLOSE cNumberOfLogs
DEALLOCATE cNumberOfLogs
SELECT LogDate, ErrorMessage FROM #ErrorLog
DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs
References
- Script: get the SQL cluster failover time and node name (SQL Geek Pro)
- Monitoring AlwaysOn health with PowerShell, part 2 (SQL AlwaysOn blog). The post covers the
Test-SqlDatabaseReplicaStatecmdlet, which checks the state of the replicas.