3

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:

enter image description here

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.

Improve this question
2
  • 2
    What does sp_readerrorlog says ? Did you thoroughly checked event viewer ?
    – Shanky
    Commented Oct 20, 2020 at 15:21
  • 1
    You say the failover is not occurring automatically, but are you able to manually fail over choosing the node? Have you checked if everything is ok using the Validate cluster option on the Failover Cluster Manager? Also, have you checked the critical events for each cluster resource (disks, network, role, node)? They seem to have its own critical log events that might not be listed on the Recent Cluster Events page.
    – Ronaldo
    Commented Oct 20, 2020 at 16:43

1 Answer 1

Reset to default
4

One clue would be to look in the system_health extended events session, specifically sql_exit_invoked event. 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.