Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to get failover Information for Cluster within a span of time Expand / Collapse
Author
Message
Posted Friday, July 12, 2013 3:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:55 AM
Points: 54, Visits: 324
Hi Guys,

Please help with a script to get Cluster node failover information within a span of time.

Below script gives information like current node, Last node and What time it failed over for the last time.


USE tempdb

DECLARE @AFTERRESTART VARCHAR(50)

DECLARE @BEFORERESTART VARCHAR(50)

DECLARE @TIME VARCHAR(100)

CREATE TABLE #TBL_FAILOVER_INFO (C1 VARCHAR(100),C2 VARCHAR(15),C3 VARCHAR (200), C4 SMALLINT IDENTITY (0,1))

INSERT INTO #TBL_FAILOVER_INFO

EXEC master.dbo.xp_readerrorlog 0, 1, 'The NETBIOS name of the local node that is running the server is', NULL, NULL, NULL

INSERT INTO #TBL_FAILOVER_INFO

EXEC master.dbo.xp_readerrorlog 1, 1, 'The NETBIOS name of the local node that is running the server is', NULL, NULL, NULL

SET @AFTERRESTART = (SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(C3 ,67,100)),70,100)) AS TEXT FROM #TBL_FAILOVER_INFO WHERE C4 =0)

SET @BEFORERESTART = (SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(C3 ,67,100)),70,100)) AS TEXT FROM #TBL_FAILOVER_INFO WHERE C4 =1)

SET @TIME = (SELECT C1 FROM #TBL_FAILOVER_INFO WHERE C4 = 0)

SELECT @AFTERRESTART AS 'CURRENT NODE' , @BEFORERESTART AS 'PREVIOUS NODE' , 'FAIL OVER STATUS' =

CASE

WHEN @AFTERRESTART = @BEFORERESTART

THEN 'RESTARTED ON THE NODE:'+ @BEFORERESTART + ' APPROXIMATELY AROUND ' + @TIME

ELSE 'FAILED OVER TO THE NODE:' + @AFTERRESTART + ' APPROXIMATELY AROUND ' + @TIME

END

drop table #TBL_FAILOVER_INFO


Thanks a lot in advance..
Post #1473252
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse