One of our SQL Server instances restarts itself in a clustered environment, but it is not fail-overed. When I checked the error logs, event viewer and cluster log I'm unable to find any particular reason for what it causing these restarts, but in failover cluster manager –> Roles –> Critical events I found this error:
Cluster resource 'SQL Server' of type 'SQL Server' in clustered role
'SQL Server (MSSQLSERVER)' failed.Based on the failure policies for
the resource and role, the cluster service may try to bring the
resource online on this node or move the group to another node of the
cluster and then restart it. Check the resource and group state using
Failover Cluster Manager or the Get-ClusterResource Windows PowerShell
cmdlet.
Can I know if there is any specific reason for SQL Server restarts with the above error, or is there anything we can configure for the SQL Server error logs to capture the errors of these restarts or failovers or any audits?
Both Windows servers are not rebooted
Server is physical server
It is an Active/Passive clustered environment
Both servers are running on Windows Server 2012 R2
Microsoft SQL Server 2014 Enterprise Edition (64-bit) on Windows
I verified all 6 errorlogs, and events in event viewer, and cluster.log file also.
Best Answer
One clue would be to look in the
system_healthextended events session, specificallysql_exit_invokedevent. This will give you the time that SQL Server started to shut down, and you can look in the "shutdown_option" value for additional details.This will tell you a little bit about how the service was stopped (through T-SQL, or through the services snap-in / configuration manager).
Once you know the when and how, you can dig back in to the SQL Server error log, and / or Windows Event Viewer, from that time period and see if there are more details about what initiated the shutdown.