By: Jugal Shah | Comments (6) | Related: > Clustering
Problem
While troubleshooting a SQL Server cluster failover issue, it is essential to know the time needed for the cluster failover and the node name where SQL Server was running before the failover occurred. In this tip, I will show you the different options to find the failover time and node name where SQL Server was running before the failover over.
Solution
In a SQL Server clustered environment it is difficult to identify which node SQL Server was running on before and after the failover. This is because most of the logging is using the network name or the instance name of the virtual server. We can check it using the SQL Server Error Log and Event Viewer log.
To check the current node/host name where SQL Server is running is quite easy, you can execute the query below and it will provide the current node name.
select serverproperty('ComputerNamePhysicalNetBIOS')
Using SSMS to find out when SQL Server Restarted or Failed Over
You can find out when SQL Server restarted or failed over by checking the SQL Server error log. You can check the error log either using SSMS or by executing the XP_READERRORLOG extended stored procedure. You have to check for the below message.
"The NETBIOS name of the local node that is running the server is 'XXXXXXXX'. This is an informational message only. No user action is required."
Below is a screen shot in SQL Server Management Studio displaying a record the error message in the error log. Check for the above message in the error log two times; the first message will tell you the current host where SQL Server Instance is running. Again scan the previous error log for the same message because it will tell you the host name before SQL Server restarted.
Check out the image below as an example. The first two messages indicates that SQL Server is restarted on the same host/node (DBP2), last message indicates that SQL Server was running on host DBP1 before the failover.
Using a SQL Server Script with the xp_readerrorlog Extended Stored Procedure to Determine When SQL Server Restarted
Execute the below script to scan all of the SQL Server error log files for the 'NETBIOS' string:
SET NOCOUNT ON -- 1 - Declare variables DECLARE @numerrorlogfile int -- 2 - Create temporary table CREATE TABLE #errorLog ([LogDate] datetime, [ProcessInfo] nvarchar(20), [Text] nvarchar(max) ) -- 3 - Initialize parameters SET @numerrorlogfile = 0 -- 4 - WHILE loop to process error logs WHILE @numerrorlogfile < 5 BEGIN TRY INSERT #errorLog ([LogDate], [ProcessInfo], [Text]) EXEC master.dbo.xp_readerrorlog @numerrorlogfile, 1, N'NETBIOS', NULL, NULL, NULL, N'desc' SET @numerrorlogfile = @numerrorlogfile + 1; END TRY BEGIN CATCH SET @numerrorlogfile = @numerrorlogfile + 1; END CATCH -- 5 - Final result set SELECT LogDate,[Text] FROM #errorLog -- 6 - Clean-up temp table DROP TABLE #errorlog GO
Based on looking at this output, if the host name in both of the messages is the same then the SQL Server Service just restarted on the same host/node. If the host name is different then SQL Server failed over from that node to a new node.
Using Windows Event Viewer to Determine the Node and Host SQL Server is Active
A third option is to check Windows Event Viewer for event ID 17664. Once you have found these entries you can find the node and host name in "Description" section.
Next Steps
- If the failover or restart is un-planned, analyze the logs before SQL Start or failover to help troubleshoot the issue.
- Check the resource usage or any issue as per the host name indicated in the NET BIOS message.
- Check out all of the clustering tips on MSSQLTips.com.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips