I guess if you want it find from sql server, then you should create a job on the clustered SQL Server instance that will send an email via Database Mail OR add an entry in a table OR SQL Serve Error Log whenever a restart of services occurs. This way you could always know which was the last active node. For e.g. On start of service an email would be generated to concerned persons, would log an entry in the SQL Error Log with something like declare @message varchar(200) SET @message = 'The Current Node is :- '+convert(varchar(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')); exec sys.xp_logevent 60000,@message; I could easily find out through emails that which node was/is active when, and can act accordingly. Moreover, If wanted to know the fail-overs happened by reading the error log (I guess this script can give me last 6 fail-overs instances on one node keeping in mind the default number of error files is 6 and there is no manual cycling of error files) DECLARE @DSQL NVARCHAR(MAX) SET NOCOUNT ON; CREATE TABLE #Logs ( Archive INT, Archivedate DATETIME, FileSize INT ) INSERT #Logs EXEC master.dbo.xp_enumerrorlogs CREATE TABLE #Log ( LogDate DATETIME, ProcessInfo VARCHAR(255), [Text] NVARCHAR(MAX) ) DECLARE @CurrentLog INT, @MaxLog INT SELECT @CurrentLog = MIN(Archive), @MaxLog = MAX(Archive) FROM #Logs WHILE @CurrentLog @CurrentLog END SELECT * FROM #Log DROP TABLE #Logs, #Log After running the above script on all my nodes, I could easily sort out which node was active at which time. This should be a pro-active exercise rather than reactive.
This will allow you to check the errorlog details in T-SQL: DECLARE @results AS TABLE (LogDate datetime, ProcessInfo varchar(255), LogText nvarchar(max)) DECLARE @TextToFind varchar(255), @SQL_Or_Agent tinyint, @ErrorLogNumber tinyint SELECT @TextToFind = 'The NETBIOS name of the local node that is running the server is', -- This is the error log entry for a SQL 2008 cluster node, may be different for other versions @SQL_Or_Agent = 1, -- 1 = SQL ErrorLog, 2 = SQLAgent Log @ErrorLogNumber = 1 -- 0 = Current Logfile, 1 = 1st Archive Log file, 2 = 2nd Archive Log file .... /* Grab the results of the readerrorlog procedure in a table variable to allow for further processing */ INSERT INTO @results EXEC sys.sp_readerrorlog @p1 = @ErrorLogNumber, @p2 = @SQL_Or_Agent, @p3 = @TextToFind SELECT SUBSTRING(LogText, 3, CHARINDEX('''', LogText, 3) - 3) AS DiscoveredNode,-- Awful hack to display the node name, because not everyone has a nice string split function CASE @SQL_Or_Agent WHEN 1 THEN 'SQL ErrorLog' WHEN 2 THEN 'SQLAgent Log' ELSE '#### ERROR - BAD INPUT ####' END AS LogType, @ErrorLogNumber AS LogFileNumber FROM (SELECT REPLACE(LogText, @TextToFind, '') LogText FROM @results r) r I tested this on a SQL 2008 Cluster, you may need to change the @TextToFind parameter for other SQL Server versions, I'm not too sure what text is written to the error logs for them, although I imagine it will be the same from 2005 onwards.