Every now and then I sit next to DBA to learn some of their tricks. One of this recent learning is what I plan to share here. It is tough to learn everything all by ourselves, so learning is best done by sharing. There are many incidents with SQL cluster where we need to know what is happening with cluster and the best way to know history about cluster related event is to generate a cluster.log file. Event log has events related to Failover clustering but it is not very verbose. Typically, when SQL Resource doesn’t come online, I look at cluster log.
The command to generate cluster log depends on operating system the command with change. I am not an expert on cluster, but I generally use the commands to generate log and see if there is any glaring error message. In Windows Server 2003, the cluster log was written automatically, but Windows 2008 onwards the needs to be generated. I think it was a good decision by Microsoft to not to write on this all the time. Generation of cluster log is done using the cluster.exe command. Here are the steps
- From one of the nodes of the cluster, open a Command Prompt with Administrator rights (Right click > Run As Administrator)
- The easiest command to generate the log is to type cluster log /g and hit enter. Parameter g stands for generate.
- A file with name cluster.log will be generated and stored in the %windir%\Cluster\Reports directory on each node of the cluster.
Here is the output on my lab machine having 4 nodes cluster. If any of the nodes is not working, it would continue with another node.
Here are the files generated in C:\Windows\Cluster\Reports
In later version, cluster.exe is not available (unless you install legacy components) and then you have to use PowerShell method of generating cluster log. The command which is required is Get-ClusterLog
PS C:\Windows\system32> Get-ClusterLog
The term ‘get-clusterlog’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:15
+ get-clusterlog <<<<
+ CategoryInfo : ObjectNotFound: (get-clusterlog:String) , CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
If this is the error, then you need to import failover cluster module using “Import-Module FailoverClusters” command. Here is the screenshot. Since we have a 4 node cluster, we are seeing cluster.log four times.
Here is the syntax of the command from Get-Help Get-ClusterLog command.
Get-ClusterLog [-InputObject <psobject>] [[-Node] <StringCollection>] [-Cluster <string>] [-Destination <string>] [-TimeSpan <UInt32>] [<CommonParameters>]
You can customize the file based on your need but to be very honest, I have never used the additional parameters.
Have you come across a situation where cluster log has helped you? I am sure in future more of these will get added and your experience in using the same would be of great help to others too. So do let us know if you did something similar in your servers too.
Reference: Pinal Dave (https://blog.sqlauthority.com)