How to find whether SQL Server has failed over in cluster by looking at SQL error log

Yesterday while going through MSDN Forums I saw a question on how to find whether the SQL Server Instance in Cluster has failed over, using only SQL Error Logs. As all of you knows, If  SQL Server restarts on the same node or fails over to the other node, then we will see a new SQL Server Error log getting generated. Now the task is to find whether it was a running on the same node or not, as before. If you know which node SQL was running earlier then you have multiple methods to check which node SQL is running now and there by find whether it was a failover or not. If you dont know which node SQL was running then you can do the below.

Open the recent SQL Server error log and search for the below message

“The NETBIOS name of the local node that is running the server is ‘XXXXXXXXXX’. This is an informational message only. No user action is required.”

Now the ‘XXXXXXXXXX’ will be the name of the physical node where SQL Server is currently running. All you have to do is to open the previous error log and see the same message. If the server names are same then it is a restart on the same node. If the server names are different then it is a failover.

Happy failover troubleshooting !!

2 thoughts on “How to find whether SQL Server has failed over in cluster by looking at SQL error log

  1. Pingback: How to know if SQL has failed over in cluster through a script « SQLLearnings

  2. Quickest ways to do this without opening the SQL Errorlog is to either read the Default trace or use Powershell to parse the event logs and look for the “failover” string with the SQL resource that you are interested in.

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