Troubleshooting AlwaysOn availability groups

Collect the SQL Server logs, the AlwaysOn_health files and the WSFC logs (Get-ClusterLog) to troubleshoot an availability group.

Diagnosing the availability groups

  1. Collect the SQL Server error logs from the SQL Server log directory.
  2. Collect the files of the AlwaysOn_health event session: the alwayson_health*.xel files, in the same SQL Server log directory.

You can find the SQL Server log directory with this query:

SELECT
	LEFT(CAST(SERVERPROPERTY('ErrorLogFileName') as nvarchar(max)),
		LEN(CAST(SERVERPROPERTY('ErrorLogFileName') as nvarchar(max))) - LEN('ERRORLOG'));

Diagnosing WSFC

You can export the cluster logs with the Get-ClusterLog cmdlet:

Import-Module FailoverClusters
Get-ClusterLog -UseLocalTime -Destination C:\temp\

Microsoft also provides a tool that collects the cluster diagnostics and logs. You can install it by following these instructions.

Install-PackageProvider NuGet -Force
Install-Module PrivateCloud.DiagnosticInfo -Force
Import-Module PrivateCloud.DiagnosticInfo -Force
Install-Module -Name MSFT.Network.Diag

Then call the cmdlet to generate the report into a directory:

New-Item -Name clusterdiag -Path c:\temp -ItemType Directory -Force
Get-SddcDiagnosticInfo -ClusterName cluster -WriteToPath c:\temp\clusterdiag
SQL Server Expertise Need help with SQL Server? Contact me