Share via


When did a cluster last failover?

Question

Sunday, April 5, 2015 9:04 AM

Im need to use T-sql to check when a sql instance was last failed over to the other node in a two node cluster?

Is this possible?

Does a SQL instance have to restart when its failed over or does it transition seamlessly?

Thanks,

Zoe

All replies (8)

Sunday, April 5, 2015 1:27 PM ✅Answered | 1 vote

Yes SQL instance have to restart when fail over occurs. You can check when was SQL restarted to know last failover.

Viabhav has already give the script to check last failover:

http://sqllearnings.com/2011/05/10/how-to-know-if-sql-has-failed-over-in-cluster-through-a-script/

You can configure alert to track failover:

http://www.practicalsqldba.com/2012/07/sql-server-how-can-i-get-notification.html

http://forum.sqlsentry.net/topic1818-tip--detecting-cluster-failover.aspx

http://www.sqlservercentral.com/Forums/Topic611393-146-1.aspx

https://ask.sqlservercentral.com/questions/92736/how-to-get-alert-when-the-sql-server-failover-occu.html


Sunday, April 5, 2015 9:39 AM | 1 vote

For the first question, check if below is useful:

http://sqllearnings.com/2011/05/10/how-to-know-if-sql-has-failed-over-in-cluster-through-a-script/

Cheers,
Vaibhav Chaudhari
[MCTS], [MCP]


Sunday, April 5, 2015 1:00 PM

Oh Brilliant thats really helpful!

Do you know where I can find the cluster logs?

Its Windows server 2003 R2 - Ive looked in C:\Windows\cluster\cluster.log  but that only shows me 1 days worth of logs - there any way to see back further?

Thanks,

Zoe


Sunday, April 5, 2015 1:31 PM | 1 vote

- there any way to see back further?

You can check in windows event viewer for past failover as well.


Sunday, April 5, 2015 1:39 PM | 1 vote

Filter event viewer to see all events triggered by cluster.

Source: ClusSvc


Sunday, April 5, 2015 1:39 PM | 1 vote

Do you know where I can find the cluster logs?

View Events and Logs for a Failover Cluster  (Applies To: Windows Server 2008 R2)

Cheers,
Vaibhav Chaudhari
[MCTS], [MCP]


Sunday, April 5, 2015 1:45 PM | 1 vote

Do you know where I can find the cluster logs?

View Events and Logs for a Failover Cluster 

Cheers,
Vaibhav Chaudhari
[MCTS], [MCP]

Vaibhav, This one is for windows 2008 failover cluster not for windows 2003 cluster :)


Sunday, April 5, 2015 2:13 PM | 1 vote

If you only want to find the failover and nothing else then you can edit the first script:

EXEC master.dbo.xp_readerrorlog 2, 1

2stands for errorlog file number and you can go up to 6 errorlogs. That is like past 7 fail overs/SQL restarts.

Hope you are not cycling the error log.