LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon