Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2012-10-26   |   Comments (6)   |   Related Tips: More > 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.


Last Updated: 2012-10-26


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

'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
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

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

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

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

SELECT SERVERPROPERTY('computernamephysicalnetbios')


Learn more about SQL Server tools