Steps to Check the Host Name for a Clustered SQL Server Instance

By:   |   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.

SQL Server Error log with NETBIOS entry

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.

SQL Server Error log with multiple NETBIOS entries

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.

SQL Server Error log output from xp_readerrorlog extended stored procedure

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.

Windows Event Viewer for event ID 17664
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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, January 12, 2016 - 4:12:43 AM - crunchy Back To Top (40402)

Could someone please help me with query to trriger DB mail whenever clutster failover happens,something like below query.

 

 

CREATE PROCEDURE [dbo].[ClusterFailover] 

 

 

AS 

BEGIN 

 

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

    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 

 

 

DECLARE @ServerProp VARCHAR(MAX)

SELECT @ServerProp= 'Dear All,

 

This is to inform that cluster failover has happend from one node to another.The current active node is '+CONVERT(VARCHAR(4000), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) + 

 

' 

 

Thanks,   

 

Note: This is a system alert message. Please do not reply to this email as it will be discarded.'

 

exec msdb.dbo.sp_send_dbmail @profile_name='Alerts', 

 @recipients ='recipients email address;',

 @subject ='!! Cluster Node Failover !!',

 @body = @ServerProp

 

-- 5 - Final result set

 

 

SELECT Top 1 LogDate,[Text] FROM #errorLog order by LogDate desc

-- 6 - Clean-up temp table

 

DROP TABLE #errorlog

END

END

 

 

GO

 

 

Tuesday, May 13, 2014 - 2:16:07 PM - k3nnyg Back To Top (30761)

'MachineName' returns the instance name not the hostname of the physical  node it is residing on currently. 

 

The code above works only if you don't regularly roate your error logs. I do so every night at midnight with an agent job running simply dbcc 'errorlog'. So the informational messsage you are searching for in your log will not be in there unless it has been restarted since the lst log rotation. Still...thanks. this is a great contribution that will work for most and the inforamtion is GREAT. 

 

thanks!

Monday, May 27, 2013 - 6:00:24 PM - Victor Brink Back To Top (25155) 
Insted of 
select serverproperty('ComputerNamePhysicalNetBIOS')

 
Books Online advises use 'MachineName'
as in 
select serverproperty('MachineName')

Monday, October 29, 2012 - 2:58:44 PM - Brady Back To Top (20135)

Good Article!

Another good DMV to use for clustered instances is sys.dm_os_cluster_nodes.

This will show you all the possible node names for the cluster.

Example, SELECT * FROM sys.dm_os_cluster_nodes

Friday, October 26, 2012 - 9:51:17 AM - Rich Holt Back To Top (20103)

Thanks, Jugal.  Good tip!

Two small things to note: 
- Use ORDER BY [LogDate] to ensure the sequence of events is preserved
- In case-sensitive collations, the query will fail because the DROP TABLE statement doesn’t match the table name

You could take this one step further, and extract the NodeName from the logs into the temp table as a computed column, saving yourself the work of searching the [Text] manually to note which NodeName the cluster landed on.  And I prefer to keep the results until I'm finished with them (what if I want to run additional queries?), so I tend to drop at the top when necessary.

My revised version: 

 

SET NOCOUNT ON
-- 1 - Declare variables
DECLARE @numerrorlogfile int;
-- 2 - Create temporary table
IF OBJECT_ID('tempdb.dbo.#errorLog') IS NOT NULL DROP TABLE #errorLog;
CREATE TABLE #errorLog
([LogDate] datetime not null,
 [ProcessInfo] nvarchar(20) not null,
 [Text] nvarchar(max) not null,
 [NodeName] as CASE --computed column to get the node name
   when
    PATINDEX(N'%The NETBIOS name of the local node that is running the server is ''%',[Text])>0
   then
    SUBSTRING([Text],
              CHARINDEX('''',[Text],1)+1,
              CHARINDEX('''',[Text], CHARINDEX('''',[Text],1) +1) - CHARINDEX('''',[Text],1) -1
              )
   else NULL
   END,
 );
-- 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, NodeName
FROM #errorLog
WHERE NodeName IS NOT NULL
ORDER BY LogDate;
GO
 

Cheers!

 

Friday, October 26, 2012 - 8:53:22 AM - Raghu G Back To Top (20102)

Another way of finding the host name of the box the SQL Server instance is actively running on is

SELECT SERVERPROPERTY('computernamephysicalnetbios')














get free sql tips
agree to terms