How to find cause of SQL Server cluster failover or SQL restart? | Find Always on failover reason from SQL Server logs?

by

This Article is about ,

How to find cause of SQL Server cluster failover or SQL restart? | Find AlwaysOn failover reason from SQL Server logs?

Why SQL Server restarted or failover in AlwaysON or SQL Server Cluster?

There are planned or unplanned outage in SQL Server. Common causes are SQL Service restart, Server reboot, AlwaysON failover, SQL Cluster failover. On production server we can schedule alert in case there are any such instance occurred.

SQL Server capture the logs for AlwaysON or Failover cluster in SQL error logs.

To identify the cause , we need to read SQL error logs or event view logs to find out cause of failover.

Below query can help to find out cause of instance occurred in SQL Server or How to find cause of SQL Server cluster failover or SQL restart? or Find Always on failover reason from SQL Server logs?

How to find SQL Service Restart reason from SQL Server logs

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 order by LogDate desc

DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs

How to find AlwaysON Failover reason from SQL Server logs?

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, 'The state of the local availability replica'
       FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID

   END 
   
CLOSE cNumberOfLogs
DEALLOCATE cNumberOfLogs

SELECT LogDate, ErrorMessage FROM #ErrorLog order by LogDate desc

DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs

Once you have reason ,you can troubleshoot the issue or if need admin team for support you can create ticket for them. Sometime due to network connectivity we get alert but failover not triggers as network connectivity back immediately.

How to find Failover of SQL cluster reason from SQL Server logs?

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, 'Failover'
       FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID

   END 
   
CLOSE cNumberOfLogs
DEALLOCATE cNumberOfLogs

SELECT LogDate, ErrorMessage FROM #ErrorLog order by LogDate desc

DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs

Failover Cluster is another SQL Server high availability technique. Sometime cluster failover happened and its logs get captured in SQL Server error logs. Most common reason, network issue, quorum issue, Cluster disk failure etc.

Once you have error details it can be resolved to make cluster issue.

Leave a Comment