Which Node SQL Cluster was Running
February 20, 2015
While troubleshooting unplanned SQL cluster failover few questions are asked commonly.
- When did the last failover occur?
- How frequently you failover (planned and unplanned)?
- 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