How to find if SQL has failed over in cluster through a script

Last month I wrote a post on how to find whether SQL Server failed over in a cluster by looking at SQL Error Log.

Today I was thinking of a way to get this information from Error Log by some queries. So I wrote a small script , which will tell what is the node SQL is currently running , the previous node and also whether the last restart was a failover or a simple restart on the same node all by reading the error log.

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 SMALLINTIDENTITY (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 TEXTFROM #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

Now to automate this process, you can create a job and schedule it to run when the SQL Agent starts and then email the results. This will help you to know whether there is a failover or restart.  Now the only loophole in this process is even if the SQL Server Agent alone is restarted , then also it will send an email.  Another way to get it emailed to you is to make a stored procedure and have it send the results of this script in an email. Now add the stored procedure as a startup stored procedure in SQL so that It runs every time SQL restarts. More about startup stored procedures here

One thought on “How to find if SQL has failed over in cluster through a script

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s