Which Node SQL Cluster was Running

February 20, 2015

While troubleshooting unplanned SQL cluster failover few questions are asked commonly.

1. When did the last failover occur?
2. How frequently you failover (planned and unplanned)?
3. Did this happen recently?

All these questions can be answered by this query.  Your output will depend on how frequently you recycle error log and retention of the error log.

You can also use this query to look for other items in your error log.

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'#ERROR') AND TYPE IN (N'U'))
BEGIN
CREATE TABLE #ERROR (
LogDate DATETIME,
ProcessInfo NVARCHAR(255),
LogText NVARCHAR(MAX)
)
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[ErrorLogList]') AND TYPE IN (N'U'))

BEGIN
CREATE TABLE ErrorLogList (intFileId INT
, dtLastChangeDate DATETIME NOT NULL
, biLogFileSize BIGINT)
END
GO

INSERT INTO ErrorLogList
EXEC master.dbo.xp_enumerrorlogs

DECLARE ErrorLog_cursor CURSOR FOR
SELECT intFileId
FROM ErrorLogList WITH (NOLOCK)

OPEN ErrorLog_cursor
DECLARE @intFileId INT
FETCH NEXT
FROM ErrorLog_cursor INTO @intFileId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO #ERROR (
[LogDate],
[ProcessInfo],
[LogText]
)

--Replacing text in the third parameter you can search for any string in your error log.
EXEC xp_readerrorlog @intFileId, 1, N'The NETBIOS name of the local node that is running the server is', N''
FETCH NEXT
FROM ErrorLog_cursor INTO @intFileId
END

CLOSE ErrorLog_cursor
DEALLOCATE ErrorLog_cursor

select Logdate AS [FailOverTime], Substring (Logtext, charindex('''', logtext) +1, ((charindex('.', logtext))- (charindex('''', logtext))-2) ) AS [ActiveNode] from #ERROR

DROP TABLE [dbo].[ErrorLogList]
Drop table #Error