Windows Cluster - Manual Failover vs Automatic Failover

article source - SQL Server Tips by Ashwin

Often, DBA's are given a task to find the RCA of SQL Server failover in cluster and it is difficult to find whether the failover is an automatic failover (due to some issue) or a manual failover (by any other dba, system admin etc).

An easy way to figure that out is to take a look at the cluster log (which is generally seen at C:\Windows\Cluster\Cluster.log on all the member nodes in cluster.
Open the log and search for these keywords “FmsMoveGroupRequest” and “FmpDoMoveGroup”. You will find entries similar to the below.

This says that it is a manual failover and not an automatic failover

1.INFO [FM] FmsMoveGroupRequest: To move group ‘a47f0c1a-fc1b-45ed-8a34-07bf59300c9c’

2.INFO [FM] FmpDoMoveGroup: Entry

3.INFO [FM] FmpMoveGroup: Entry

4.INFO [FM] FmpPickNodeFromPreferredListAtRandom: Picking node for group a47f0c1a-fc1b-45ed-8a34-07bf59300c9c [SQL2005], suggested node 1…

5.INFO [FM] FmpPickNodeFromPreferredListAtRandom: Node 1 for group a47f0c1a-fc1b-45ed-8a34-07bf59300c9c is user preferred…

6.INFO [FM] FmpPickNodeFromPreferredListAtRandom: Selected node 1 for group a47f0c1a-fc1b-45ed-8a34-07bf59300c9c…

7.INFO [FM] FmpMoveGroup: Moving group a47f0c1a-fc1b-45ed-8a34-07bf59300c9c to node 1 (1)

8.INFO [FM] FmpNotifyGroupStateChangeReason: Notifying group SQL2005 [a47f0c1a-fc1b-45ed-8a34-07bf59300c9c] of state change reason 1…

If you scroll through the log, you will find these entries which says that the manual failvover has successfully completed

1.INFO [FM] FmpCompleteMoveGroup: Exit, status = 0

2.INFO [FM] FmpMovePendingThread Exit.